# -*- coding: utf-8 -*-
from decimal import Decimal
import json
import uuid
from collections import Counter

import pytz

import app.DataService_data_integrity as DataService_data_integrity
import app.DataService_homepage as DataService_homepage
import app.DataService_kpi as DataService_kpi
import utils.config as config
from model.dataModel import betaRawDataDo, vehicleInfoMngtDo, filesCompleteness, triggerIdInfoMngt, feedbackDo
from sqlalchemy import create_engine, Column, Integer, String, func, and_, or_, distinct, text, desc, select,case
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.exc import PendingRollbackError
from bottle import Bottle, request, response, template, static_file
import sys
import os
import pandas as pd
import time
from datetime import datetime
import datetime as datetime_do
from dateutil.relativedelta import relativedelta

Base = declarative_base()

# 创建实例 连接shadowmode库
engine = create_engine(
    f"mysql+pymysql://{config.db_username}:{config.db_password}@{config.db_host}:{config.db_port}/{config.db_database}?charset=utf8",
    max_overflow=0,
    pool_size=10,  # 连接池大小
    pool_pre_ping=True
)
SessionFactory = sessionmaker(bind=engine)
session = scoped_session(SessionFactory)


def timestamp_to_str(date_time):
    # 将时间戳转换为 UTC datetime 对象
    utc_dt = datetime.fromtimestamp(date_time, pytz.utc)
    # 转换为本地时间
    local_dt = utc_dt.astimezone()  # 默认转换为本地时区
    # 格式化输出
    formatted_time = local_dt.strftime('%Y-%m-%d %H:%M:%S')
    return formatted_time


def date_str_to_timestamp(date_str):
    return int(time.mktime(time.strptime(date_str, "%Y-%m-%d")))


dataServiceApp = Bottle()


@dataServiceApp.route('/get_ota_for_software', method=['get'])
def get_ota_for_software():
    """
        [GET] /get_ota_for_software 软件质量页面，下拉版本option数据获取

        Parameters
        ----------

        Returns
        -------
        object
            JSON 数组

    """
    result = {
        "code": 200,
        "message": "core",
        "data": []
    }
    sql_ = f"""
        with data_temp as ( select 
         SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(subquery.vs,'-',-2),'_',-1 ),1,17) as ota_version
        ,SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(subquery.vs,'-',-2),'_',-1 ),1,4) as ota_version_type
        ,SUBSTRING_INDEX(SUBSTRING_INDEX(subquery.vs,'-',-2),'_',1) as ota_win_type
         from
         (
            SELECT vs
            FROM explor_kpi_tid_100030 ekt 
            UNION
            SELECT vs
            FROM explor_kpi_tid_100031 ekt 
            UNION
            SELECT vs
            FROM explor_kpi_tid_100032 ekt 
            UNION
            SELECT vs
            FROM explor_kpi_tid_100033 ekt 
            UNION
            SELECT ota_version as vs
            FROM core_exc_140007 ekt
            )subquery
            )
        select dt.ota_version 
        from data_temp dt 
        where dt.ota_version_type >='OTA5'
          and dt.ota_version_type like '%OTA%'
          and dt.ota_win_type >= 'WIN2.0'
        order by dt.ota_version
     """
    try:
        result_data = (session.execute(text(sql_)).all())
        for data in result_data:
            result["data"].append(data[0])
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


@dataServiceApp.route('/get_core_140007', method=['post'])
def get_core_140007():
    """
        [POST] /get_core_140007 软件质量监控

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
            ota版本号，逗号隔开

        Returns
        -------
        object
            JSON 数组

    """
    json_params = request.json
    start_date = json_params["start_date"]
    end_date = json_params["end_date"]
    otas = str2Array(json_params["ota_versions"])
    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and cd.ota_version in ('{"','".join(otasStrArray)}')"
    result = {
        "code": 200,
        "message": "core",
        "data": []
    }
    sql_ = f"""
            with core_date as(
                select DATE_FORMAT(FROM_UNIXTIME(ce.data_ts/1000),'%Y-%m-%d') as date_temp,
                            ce.ota_version as ota_version,
                            right(ce.uuid,12) as uuid_simple,
                            ce.uuid,
                            ce.tid,
                            ce.core_filename,
                            ota.ota_ver_stamp
                            from core_exc_140007 ce
                            left join ota_version_info_mngt ota
                              on ce.ota_version = ota.ota_version
                              and DATE_FORMAT(FROM_UNIXTIME(ce.data_ts/1000),'%Y-%m-%d') = ota.date
              
            )
            select case when t.ota_ver_stamp is null then '未知版本'
                        else t.ota_ver_stamp
                   end as ota_ver_stamp
                   ,t.core_filename
                   ,t.gs
              from (
                    select
                        cd.ota_ver_stamp,
                        cd.core_filename,
                        count(1) as gs
                    from
                        core_date cd
                    where cd.date_temp >= '{start_date}' and cd.date_temp <= '{end_date}' {otasStr} 
                    group by
                        cd.ota_ver_stamp,cd.core_filename 
                    ) t
            order by t.ota_ver_stamp,t.gs   
            """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = {}
        for data in result_data:
            if not data[0] in data_list:
                data_list[data[0]] = [{"name":data[1],"value":data[2]}]
            else:
                data_list[data[0]].append({"name":data[1],"value":data[2]})
        for key in data_list.keys():
            result["data"].append({
                "name":key,
                "children":data_list[key]
            })
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


@dataServiceApp.route('/version_trigger_detail_right_view', method=['post'])
def version_trigger_detail_right_view():
    """
        [POST] /version_trigger_detail_right_view 版本及触发详情-右侧图表数据--包含详情

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
            ota版本号，逗号隔开
        triggerId: str
            triggerId 多个用逗号隔开

        Returns
        -------
        object
            JSON 数组

        """
    json_params = request.json
    start_date = json_params["start_date"]
    end_date = json_params["end_date"]
    otas = str2Array(json_params["ota_versions"])
    # 版本触发详情不受到trigger设置影响
    trigger_ids = [] # str2Array(request.query.triggerId)
    function = int(json_params["function"])
    function_str = f"and dt.is_function = {function}" if function < 2 else f" and dt.is_function in (0,1)"
    triggerIdStr = ""
    tid_list = []
    for tid in trigger_ids:
        if len(tid) > 0:
            tid_list.append(tid)
    if len(tid_list) > 0:
        triggerIdStr = f" and dt.trigger_id in ('{"','".join(tid_list)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and dt.ota_version in ('{"','".join(otasStrArray)}')"
    result = {
        "code": 200,
        "message": "版本及触发详情-右侧图表数据--包含详情",
        "data": []
    }
    sql_ = f"""
            with data_temp as(
            select DATE_FORMAT(FROM_UNIXTIME(brd.data_ts/1000),'%Y-%m-%d') as date_temp,
            brd.ota_version as ota_version,
            right(brd.uuid,12) as uuid_simple,
            brd.uuid,
            brd.trigger_id,
            vim.vin,
            vim.vehicle_tested_by ,
            vim.int_vehicle_no,
            tiim.is_function
            from beta_raw_data brd left join vehicle_info_mngt vim on brd.uuid = vim.chery_no 
            left join trigger_id_info_mngt tiim on brd.trigger_id = tiim.trigger_id
            where tiim.is_ignore = 0
            )
            select  
            case when SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(dt.ota_version,'-',-2),'_',-1 ),1,17) = 'unknown'
            then '未知版本'
            when SUBSTRING_INDEX(SUBSTRING_INDEX(dt.ota_version, '_',-1), '-', 1) = 'OTA5.5'
            then LEFT(SUBSTRING_INDEX(dt.ota_version, '_',-1),length(SUBSTRING_INDEX(dt.ota_version, '_',-1))-3)
            else SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(dt.ota_version,'-',-2),'_',-1 ),1,17)
            end as ota_version, 
            count(dt.trigger_id) as trigger_count,
            count(distinct(dt.trigger_id)) as trigger_class
            from data_temp dt
            where dt.date_temp >= '{start_date}' and dt.date_temp <= '{end_date}' {triggerIdStr} {otasStr} {function_str}
            group by dt.ota_version
            order by dt.ota_version
            """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "ota_version": data[0],
                    "trigger_times": data[1],
                    "trigger_count": data[2]
                }
            )
        data_list_detail = {
            "hearders": ["Trigger Name"],
            "trigger_list":[],
            "table_data": []
        }
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


@dataServiceApp.route('/version_trigger_detail_right_view_comparison', method=['post'])
def version_trigger_detail_right_view_comparison():
    """
        [POST] /version_trigger_detail_right_view_comparison 版本及触发详情-右侧图表数据--数据比较

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
            ota版本号，逗号隔开
        triggerId: str
            triggerId 多个用逗号隔开

        Returns
        -------
        object
            JSON 数组

        """
    json_params = request.json
    start_date = json_params["start_date"]
    end_date = json_params["end_date"]
    otas = str2Array(json_params["ota_versions"])
    # 版本触发详情不受到trigger设置影响
    trigger_ids = []  # str2Array(request.query.triggerId)
    function = int(json_params["function"])
    function_str = f"and dt.is_function = {function}" if function < 2 else f" and dt.is_function in (0,1)"
    triggerIdStr = ""
    tid_list = []
    for tid in trigger_ids:
        if len(tid) > 0:
            tid_list.append(tid)
    if len(tid_list) > 0:
        triggerIdStr = f" and dt.trigger_id in ('{"','".join(tid_list)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and dt.ota_version in ('{"','".join(otasStrArray)}')"
    result = {
        "code": 200,
        "message": "版本及触发详情-右侧图表数据--包含详情",
        "data": []
    }
    sql_1 = f"""
            with data_temp as(
            select DATE_FORMAT(FROM_UNIXTIME(brd.data_ts/1000),'%Y-%m-%d') as date_temp,
            brd.ota_version as ota_version,
            right(brd.uuid,12) as uuid_simple,
            brd.uuid,
            brd.trigger_id,
            vim.vin,
            vim.vehicle_tested_by ,
            vim.int_vehicle_no,
            tiim.is_function
            from beta_raw_data brd left join vehicle_info_mngt vim on brd.uuid = vim.chery_no 
            left join trigger_id_info_mngt tiim on brd.trigger_id = tiim.trigger_id
            where tiim.is_ignore = 0
            )
            select 
            case when SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(dt.ota_version,'-',-2),'_',-1 ),1,17) = 'unknown'
            then '未知版本'
            when SUBSTRING_INDEX(SUBSTRING_INDEX(dt.ota_version, '_',-1), '-', 1) = 'OTA5.5'
            then LEFT(SUBSTRING_INDEX(dt.ota_version, '_',-1),length(SUBSTRING_INDEX(dt.ota_version, '_',-1))-3)
            else SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(dt.ota_version,'-',-2),'_',-1 ),1,17)
            end as ota_version, 
            dt.trigger_id, count(dt.trigger_id) as trigger_count
            from data_temp dt
            where dt.date_temp >= '{start_date}' and dt.date_temp <= '{end_date}' {triggerIdStr} {otasStr} {function_str}
             group by dt.ota_version,dt.trigger_id
            order by dt.ota_version
            """

    try:
        result_data_1 = (session.execute(text(sql_1)).all())
        data_list = []
        data_list_detail = {
            "hearders": ["Trigger Name"],
            "trigger_list":[],
            "table_data": []
        }
        for data_1 in result_data_1:
            if data_1[0] not in data_list_detail["hearders"]:
                data_list_detail["hearders"].append(data_1[0])
            if data_1[1] not in data_list_detail["trigger_list"]:
                data_list_detail["trigger_list"].append(data_1[1])
        for triggerId in data_list_detail["trigger_list"]:
            row_data = {
                "Trigger Name":triggerId
            }
            for version in data_list_detail["hearders"]:
                find_ = False
                for data_d in result_data_1:
                    if data_d[0] == version and data_d[1] == triggerId:
                        row_data[version] = data_d[2]
                        find_ = True
                if find_ is False and version != "Trigger Name":
                    row_data[version] = 0
            data_list_detail["table_data"].append(row_data)
        result["data"] = data_list_detail
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


@dataServiceApp.route('/trigger_detail_by_vehicle', method=['POST'])
def trigger_detail_by_vehicle():
    """
        [POST] /trigger_detail_by_vehicle 单车触发详情-车辆统计
                还需要加x，y轴统计

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
            ota版本号，逗号隔开
        trigger_id: str
            triggerId 多个用逗号隔开
        vehicles: str
            车辆uuid后12位，多个用逗号隔开

        Returns
        -------
        object
            JSON 数组
              [{
                    trigger_id:0,
                    trigger_count:0
                }]
    """
    json_params = request.json
    start_date = json_params["start_date"]
    end_date = json_params["end_date"]
    vehicles = str2Array(json_params["vehicles"])
    otas = str2Array(json_params["ota_versions"])
    trigger_ids = str2Array(json_params["triggerId"])

    triggerIdStr = ""
    tid_list = []
    for tid in trigger_ids:
        if len(tid) > 0:
            tid_list.append(tid)
    if len(tid_list) > 0:
        triggerIdStr = f" and dt.trigger_id in ({",".join(tid_list)})"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and  dt.ota_version in ('{"','".join(otasStrArray)}')"

    vehiclesStr = ""
    vehiclesArray = []
    for vehicle in vehicles:
        if len(vehicle) > 0:
            vehiclesArray.append(vehicle)
    if len(vehiclesArray) > 0:
        in_cnd = "','".join(vehiclesArray)
        vehiclesStr = f" and dt.uuid_simple in ('{in_cnd}')"
    result = {
        "code": 200,
        "message": "版本号热力图",
        "data": []
    }
    result = {
        "code": 200,
        "message": "trigger",
        "data": []
    }
    sql_ = f"""
            with data_temp as(
            select DATE_FORMAT(FROM_UNIXTIME(brd.data_ts/1000),'%Y-%m-%d') as date_temp,
            brd.ota_version as ota_version,
            right(brd.uuid,12) as uuid_simple,
            brd.uuid,
            brd.trigger_id ,
            IF(ISNULL(vim.vin),right(brd.uuid,12),vim.vin) as vin,
            vim.vehicle_tested_by ,
            vim.int_vehicle_no
            from beta_raw_data brd 
            left join vehicle_info_mngt vim on brd.uuid = vim.chery_no
            left join trigger_id_info_mngt tiim on brd.trigger_id = tiim.trigger_id
            where tiim.is_ignore = 0
            ),
            day_count_t as (
                select dt.date_temp,
                dt.vin,
                count(dt.trigger_id) as trigger_count
                from data_temp dt
                where dt.date_temp >= '{start_date}' and dt.date_temp <= '{end_date}' {triggerIdStr} {otasStr} {vehiclesStr}
                group by dt.date_temp,dt.vin
                order by dt.date_temp
            ),
            day_count as (
            select dct.date_temp ,sum(dct.trigger_count) day_count
            from day_count_t dct
            group by dct.date_temp
            order by dct.date_temp
            ),
            vin_count as (
            select dct.vin ,sum(dct.trigger_count) vin_count
            from day_count_t dct
            group by dct.vin
            )
            select dt.date_temp,
            (case when max(dt.vin) is null or max(dt.vin) = '' then max(dt.uuid_simple) else max(dt.vin) end) vin, 
            count(dt.trigger_id ) times, max(dt.uuid_simple) uuid, max(dt.ota_version) as ota_version,
            (case when max(dt.vehicle_tested_by) is null or max(dt.vehicle_tested_by) = '' then '其他' else max(dt.vehicle_tested_by) end) class_name,
            (case when max(dt.int_vehicle_no) is null or max(dt.int_vehicle_no) = '' then max(dt.uuid_simple) else max(dt.int_vehicle_no) end) user_name,
            max(dc.day_count) day_count,max(vc.vin_count) vin_count
            from data_temp dt 
            left join day_count dc on dc.date_temp = dt.date_temp
            left join vin_count vc on vc.vin = dt.vin
            where  dt.date_temp >= '{start_date}' and dt.date_temp <= '{end_date}' {triggerIdStr} {otasStr} {vehiclesStr}
            group by dt.date_temp,dt.vin 
            order by dt.date_temp
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "date": data[0],
                    "vin": data[1],
                    "times": data[2],
                    "uuid": data[3],
                    "ota_version": data[4],
                    "class_name": data[5],
                    "user_name": data[6],
                    "day_count": int(data[7]),
                    "vin_count": int(data[8]),
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


@dataServiceApp.route('/trigger_detail_by_vehicle_detail', method=['post'])
def trigger_detail_by_vehicle_detail():
    """
        [POST] /trigger_detail_by_vehicle_detail 单车触发详情-triggerId统计-数据下钻

        Parameters
        ----------
        date : str
            时间 2025-01-01
        any_id: str
            triggerId 多个用逗号隔开

        Returns
        -------
        object
            JSON 数组
              [{
                    trigger_id:0,
                    trigger_count:0
                }]
    """
    json_params = request.json
    date = json_params["date"]
    any_id = json_params["any_id"]
    otas = str2Array(json_params["ota_versions"])
    trigger_ids = str2Array(json_params["triggerId"])

    triggerIdStr = ""
    tid_list = []
    for tid in trigger_ids:
        if len(tid) > 0:
            tid_list.append(tid)
    if len(tid_list) > 0:
        triggerIdStr = f" and dt.trigger_id in ('{"','".join(tid_list)}')"
    result = {
        "code": 200,
        "message": "版本号热力图",
        "data": []
    }
    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and  dt.ota_version in ('{"','".join(otasStrArray)}')"
    result = {
        "code": 200,
        "message": "trigger",
        "data": []
    }
    sql_ = f"""
            with data_temp as(
            select DATE_FORMAT(FROM_UNIXTIME(brd.data_ts/1000),'%Y-%m-%d') as date_temp,
            brd.ota_version as ota_version,
            right(brd.uuid,12) as uuid_simple,
            brd.uuid,
            brd.trigger_id ,
            vim.vin,
            vim.vehicle_tested_by ,
            vim.int_vehicle_no
            from beta_raw_data brd left join vehicle_info_mngt vim on brd.uuid = vim.chery_no
            left join trigger_id_info_mngt tiim on brd.trigger_id = tiim.trigger_id
            where tiim.is_ignore = 0
            )
            select dt.trigger_id ,count(1) count_num,
            (case when max(dt.vin) is null or max(dt.vin) = '' then max(dt.uuid_simple) else max(dt.vin) end) vin
            from data_temp dt
            where dt.date_temp = '{date}'  
            and ( dt.uuid_simple = '{any_id}' or dt.vin='{any_id}' or dt.int_vehicle_no='{any_id}') {triggerIdStr} {otasStr}
            group by dt.trigger_id 
            order by dt.trigger_id
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "trigger_id": data[0],
                    "times": data[1],
                    "vehicle": data[2],
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


@dataServiceApp.route('/trigger_detail_by_trigger', method=['POST'])
def trigger_detail_by_trigger():
    """
        [POST] /trigger_detail_by_trigger 单车触发详情-触发统计
                还需要加x，y轴统计

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
            ota版本号，逗号隔开
        trigger_id: str
            triggerId 多个用逗号隔开
        vehicles: str
            车辆uuid后12位，多个用逗号隔开
        Returns
        -------
        object
            JSON 数组
              [{
                    trigger_id:0,
                    trigger_count:0
                }]
    """
    json_params = request.json
    start_date = json_params["start_date"]
    end_date = json_params["end_date"]
    vehicles = str2Array(json_params["vehicles"])
    otas = str2Array(json_params["ota_versions"])
    trigger_ids = str2Array(json_params["triggerId"])

    triggerIdStr = ""
    tid_list = []
    for tid in trigger_ids:
        if len(tid) > 0:
            tid_list.append(tid)
    if len(tid_list) > 0:
        triggerIdStr = f" and dt.trigger_id in ('{"','".join(tid_list)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and  dt.ota_version in ('{"','".join(otasStrArray)}')"

    vehiclesStr = ""
    vehiclesArray = []
    for vehicle in vehicles:
        if len(vehicle) > 0:
            vehiclesArray.append(vehicle)
    if len(vehiclesArray) > 0:
        in_cnd = "','".join(vehiclesArray)
        vehiclesStr = f" and (dt.uuid_simple in ('{in_cnd}') or dt.vin in ('{in_cnd}') or dt.int_vehicle_no in ('{in_cnd}'))"
    result = {
        "code": 200,
        "message": "版本号热力图",
        "data": []
    }
    result = {
        "code": 200,
        "message": "trigger",
        "data": []
    }
    sql_ = f"""
            with data_temp as(
            select DATE_FORMAT(FROM_UNIXTIME(brd.data_ts/1000),'%Y-%m-%d') as date_temp,
            brd.ota_version as ota_version,
            right(brd.uuid,12) as uuid_simple,
            brd.uuid,
            brd.trigger_id ,
            vim.vin,
            vim.vehicle_tested_by ,
            vim.int_vehicle_no
            from beta_raw_data brd left join vehicle_info_mngt vim on brd.uuid = vim.chery_no
            left join trigger_id_info_mngt tiim on brd.trigger_id = tiim.trigger_id
            where tiim.is_ignore = 0
            ),
            day_count_t as (
                select dt.date_temp,
                dt.trigger_id,
                count(dt.trigger_id) as trigger_count
                from data_temp dt
                where dt.date_temp >= '{start_date}' and dt.date_temp <= '{end_date}' {triggerIdStr} {otasStr} {vehiclesStr}
                group by dt.date_temp,dt.trigger_id
                order by dt.date_temp
            ),
            day_count as (
            select dct.date_temp ,sum(dct.trigger_count) day_count
            from day_count_t dct
            group by dct.date_temp
            order by dct.date_temp
            ),
            trigger_count as (
            select dct.trigger_id ,sum(dct.trigger_count) trigger_count
            from day_count_t dct
            group by dct.trigger_id
            )
            select dt.date_temp,
            dt.trigger_id,
            count(dt.trigger_id ) times,
            max(dc.day_count) day_count,
            max(tc.trigger_count) trigger_count,
             max(dt.ota_version) as ota_version,
             max(dt.uuid_simple) uuid,
            (case when max(dt.vin) is null or max(dt.vin) = '' then max(dt.uuid_simple) else max(dt.vin) end) vin, 
            (case when max(dt.vehicle_tested_by) is null or max(dt.vehicle_tested_by) = '' then '其他' else max(dt.vehicle_tested_by) end) class_name,
            (case when max(dt.int_vehicle_no) is null or max(dt.int_vehicle_no) = '' then max(dt.uuid_simple) else max(dt.int_vehicle_no) end) user_name
            from data_temp dt 
            left join day_count dc on dc.date_temp = dt.date_temp
            left join trigger_count tc on tc.trigger_id = dt.trigger_id
            where dt.date_temp >= '{start_date}' and dt.date_temp <= '{end_date}' {triggerIdStr} {otasStr} {vehiclesStr}
            group by dt.date_temp,dt.trigger_id 
            order by dt.date_temp
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "date": data[0],
                    "trigger_id": data[1],
                    "vin": data[7],
                    "times": data[2],
                    "uuid": data[6],
                    "ota_version": data[5],
                    "class_name": data[8],
                    "user_name": data[9],
                    "day_count": int(data[3]),
                    "trigger_count": int(data[4]),
                }
            )
        result["data"] = data_list

        print(sql_)

        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


@dataServiceApp.route('/trigger_detail_by_trigger_detail', method=['post'])
def trigger_detail_by_trigger_detail():
    """
        [POST] /trigger_detail_by_trigger_detail 单车触发详情-triggerId统计-数据下钻 以车辆为维度

        Parameters
        ----------
        date : str
            时间 2025-01-01
        any_id: str
            triggerId 多个用逗号隔开

        Returns
        -------
        object
            JSON 数组
              [{
                    trigger_id:0,
                    trigger_count:0
                }]
    """
    json_params = request.json
    date = json_params["date"]
    any_id = json_params["any_id"]
    otas = str2Array(json_params["ota_versions"])
    vehicles = str2Array(json_params["vehicles"])
    vehiclesStr = ""
    vehiclesArray = []
    for vehicle in vehicles:
        if len(vehicle) > 0:
            vehiclesArray.append(vehicle)
    if len(vehiclesArray) > 0:
        in_cnd = "','".join(vehiclesArray)
        vehiclesStr = f" and (dt.uuid_simple in ('{in_cnd}') or dt.vin in ('{in_cnd}') or dt.int_vehicle_no in ('{in_cnd}'))"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and  dt.ota_version in ('{"','".join(otasStrArray)}')"
    result = {
        "code": 200,
        "message": "版本号热力图",
        "data": []
    }
    result = {
        "code": 200,
        "message": "trigger",
        "data": []
    }
    sql_ = f"""
            
            with data_temp as(
            select DATE_FORMAT(FROM_UNIXTIME(brd.data_ts/1000),'%Y-%m-%d') as date_temp,
            brd.ota_version as ota_version,
            right(brd.uuid,12) as uuid_simple,
            brd.uuid,
            brd.trigger_id ,
            if(vim.vin is null,right(brd.uuid,12),vim.vin) as vin,
            if(vim.vehicle_tested_by is null,'其他',vim.vehicle_tested_by) as vehicle_tested_by,
            if(vim.int_vehicle_no is null,'其他',vim.int_vehicle_no) as int_vehicle_no
            from beta_raw_data brd left join vehicle_info_mngt vim on brd.uuid = vim.chery_no
            left join trigger_id_info_mngt tiim on brd.trigger_id = tiim.trigger_id
            where tiim.is_ignore = 0
            )
            select 
            dt.trigger_id,
            count(1) count_num,
            (case when max(dt.vin) is null or max(dt.vin) = '' then max(dt.uuid_simple) else max(dt.vin) end) vin
            ,max(dt.uuid_simple) uuid
            ,max(dt.int_vehicle_no) user_name
            from data_temp dt
            where dt.date_temp = '{date}'  
            and dt.trigger_id={any_id} {vehiclesStr} {otasStr}
            group by dt.vin 
            order by dt.vin
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "trigger_id": data[0],
                    "times": data[1],
                    "vin": data[2],
                    "uuid": data[3],
                    "user_name": data[4],
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


@dataServiceApp.route('/trigger_detail_missing_by_vehicle_detail', method=['post'])
def trigger_detail_missing_by_vehicle_detail():
    """
        [POST] /trigger_detail_missing_by_vehicle_detail 触发详情-trigger文件缺失的车辆次数统计-triggerId统计-数据下钻

        Parameters
        ----------
        date : str
            时间 2025-01-01
        any_id: str
            triggerId

        Returns
        -------
        object
            JSON 数组
              [{
                    "trigger_id": ,
                    "times": ,
                    "vehicle": ,
                }]
    """
    return DataService_data_integrity.trigger_detail_missing_by_vehicle_detail(request,session)


@dataServiceApp.route('/trigger_id_count_detail_cooling', method=['post'])
def trigger_id_count_detail_cooling():
    """
        [POST] /trigger_id_count_detail_cooling  软件质量 冷却期内抑制掉统计,数据下钻，查询某一天每个trigger触发多少次 适用上云

        Parameters
        ----------
        date : str
            时间 2025-01-01
        ota_versions :str
            ota版本号，多个逗号隔开
        Returns
        -------
        object
            JSON 数组
              [{
                    trigger_id:0,
                    count:0,//触发计数
                }]
    """
    date = request.json["date"]
    otas_str: str = ""
    otas_str_array = str2Array(request.json["ota_versions"])
    if len(otas_str_array) > 0:
        otas_str = f" and  dt.ota_version in ('{"','".join(otas_str_array)}')"
    result = {
        "code": 200,
        "message": "trigger",
        "data": []
    }
    sql_ = f"""
            with data_temp as (
                select DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d')  as date_temp,
                ekt.vs as ota_version,
                ekt.ignoredTrigInfoList trigger_list
                from explor_kpi_tid_100030 ekt
                where LENGTH(ekt.ignoredTrigInfoList) > 2
                )
                select dt.trigger_list
                from data_temp dt 
                where dt.date_temp = '{date}' {otas_str}
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        triggers = []
        for data in result_data:
            json_str = data[0]
            jsonArray = json.loads(json_str)
            for trigger in jsonArray:
                triggers.append(trigger[1])
        triggers.sort()
        triggers_counts = Counter(triggers)
        trigger_out = []
        for tc in triggers_counts.items():
            trigger_out.append({"trigger_id": tc[0], "count": tc[1]})
        result["data"]=trigger_out
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


@dataServiceApp.route('/trigger_id_count_detail_delete', method=['post'])
def trigger_id_count_detail_delete():
    """
        [POST] /trigger_id_count_detail_delete  软件质量 磁盘空间不足删除统计,数据下钻，查询某一天每个trigger触发多少次 适用上云

        Parameters
        ----------
        date : str
            时间 2025-01-01
        ota_versions :str
            ota版本号，多个逗号隔开
        Returns
        -------
        object
            JSON 数组
              [{
                    trigger_id:0,
                    count:0,//触发计数
                }]
    """
    date = request.json["date"]
    otas_str: str = ""
    otas_str_array = str2Array(request.json["ota_versions"])
    if len(otas_str_array) > 0:
        otas_str = f" and  dt.ota_version in ('{"','".join(otas_str_array)}')"
    result = {
        "code": 200,
        "message": "trigger",
        "data": []
    }
    sql_ = f"""
            with data_temp as (
                select DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d')  as date_temp,
                ekt.vs as ota_version,
                ekt.file trigger_list 
                from explor_kpi_tid_100033 ekt
                where LENGTH(ekt.file) > 2
                )
                select dt.trigger_list
                from data_temp dt 
                where dt.date_temp = '{date}' {otas_str}
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        triggers = []
        for data in result_data:
            json_str = data[0] #获取到每条数组字符串
            jsonArray = json.loads(json_str) # 转为json数组
            for trigger in jsonArray:
                # 每个文件名字提取trigger_id
                triggers.append(trigger.split("_")[-1].split(".")[0])
        triggers.sort()
        triggers_counts = Counter(triggers)
        trigger_out = []
        for tc in triggers_counts.items():
            trigger_out.append({"trigger_id": tc[0], "count": tc[1]})
        result["data"]=trigger_out
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


@dataServiceApp.route('/trigger_id_count_by_ota', method=['post'])
def trigger_id_count_by_ota():
    """
        [POST] /trigger_id_count_by_ota 根据ota版本和时间段查询某个triggerId的数量 适用上云

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
            ota版本号，多个逗号隔开
        trigger_id: str
            triggerId 多个用逗号隔开

        Returns
        -------
        object
            JSON 数组
              [{
                    date_temp:'2025-1-1',
                    trigger_id:0,
                    trigger_count:0,//触发计数
                }]
    """
    json_params = request.json
    start_date = json_params["start_date"]
    end_date = json_params["end_date"]
    trigger_id_list = str2Array(json_params["trigger_id"])
    if len(trigger_id_list) == 0:
        return json.dumps({
            "code": 500,
            "message": "未传入triggerId",
            "data": []
        }, ensure_ascii=False)
    otas_str: str = ""
    otas_str_array = str2Array(json_params["ota_versions"])
    if len(otas_str_array) > 0:
        otas_str = f" and  dt.ota_version in ('{"','".join(otas_str_array)}')"
    result = {
        "code": 200,
        "message": "trigger",
        "data": []
    }
    query_sql_list = []
    for trigger_id in trigger_id_list:
        if trigger_id == "100030":
            sql_ = f"""
                    with data_temp as (
                    select DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d')  as date_temp,
                    ekt.vs as ota_version,
                    JSON_LENGTH(ignoredTrigInfoList) as ignoredTrigInfoListCnt,
                    ekt.vin,
                    ekt.tid from explor_kpi_tid_{trigger_id} ekt
                    where
                    ignoredTrigInfoList is not null and ignoredTrigInfoList <> '' 
                    )
                    select dt.date_temp, 
                    max(dt.ota_version),
                    dt.tid trigger_id
                    ,sum(dt.ignoredTrigInfoListCnt) as tri100032gger_count 
                    ,round(sum(dt.ignoredTrigInfoListCnt) / count(DISTINCT dt.vin)) as avg
                    from data_temp dt
                    where dt.date_temp >= '{start_date}' and dt.date_temp <= '{end_date}' {otas_str}
                    group by dt.date_temp,dt.tid 
                    order by dt.date_temp,dt.tid 
                """
        elif trigger_id == "100033":
            sql_ = f"""
                    with data_temp as (
                    select DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d')  as date_temp,
                    ekt.vs as ota_version,
                    JSON_LENGTH(file) as fileCnt,
                    ekt.vin,
                    ekt.tid from explor_kpi_tid_{trigger_id} ekt
                    where
                        file is not null and file <> ''
                    )
                    select dt.date_temp, 
                    max(dt.ota_version),
                    dt.tid trigger_id
                    ,sum(dt.fileCnt) as tri100032gger_count 
                    ,round(sum(dt.fileCnt) / count(DISTINCT dt.vin)) as avg
                    from data_temp dt
                    where dt.date_temp >= '{start_date}' and dt.date_temp <= '{end_date}' {otas_str}
                    group by dt.date_temp,dt.tid 
                    order by dt.date_temp,dt.tid 
                """
        else:
            sql_ = f"""
                    with data_temp as (
                    select DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d')  as date_temp,
                    ekt.vs as ota_version,
                    ekt.vin,
                    ekt.tid from explor_kpi_tid_{trigger_id} ekt
                    )
                    select dt.date_temp, 
                    max(dt.ota_version),
                    dt.tid trigger_id
                    ,count(dt.ota_version) as tri100032gger_count 
                    ,round(count(dt.ota_version) / count(DISTINCT dt.vin)) as avg
                    from data_temp dt
                    where dt.date_temp >= '{start_date}' and dt.date_temp <= '{end_date}' {otas_str}
                    group by dt.date_temp,dt.tid 
                    order by dt.date_temp,dt.tid 
                """
        query_sql_list.append(sql_)
    try:
        for sql_text in query_sql_list:
            result_data = (session.execute(text(sql_text)).all())
            for triggerData in result_data:
                result["data"].append(
                    {
                        "date_temp": triggerData[0],
                        "ota_version": triggerData[1],
                        "trigger_id": triggerData[2],
                        "trigger_count": int(triggerData[3]),
                        "daily_vin_avg": int(triggerData[4])
                    }
                )
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


@dataServiceApp.route('/ai_function_kpi_all_vehicle', method=['get'])
def ai_function_kpi_all_vehicle():
    """
        [GET] /ai_function_kpi_all_vehicle 获取所有车辆数

        Parameters
        ----------

        Returns
        -------
        num
    """
    result = {
        "code": 200,
        "message": "获取所有车辆数",
        "data": []
    }
    try:
        sql_text = f"""
                        with 
                        vid_num as(
                        select ekt.vid as vid_2 from explor_kpi_tid_100002 ekt
                        union
                        select ekt.vid as vid_2 from explor_kpi_tid_100003 ekt
                        union
                        select ekt.vid as vid_2 from explor_kpi_tid_100006 ekt
                        union
                        select ekt.vid as vid_2 from explor_kpi_tid_100009 ekt
                        union
                        select ekt.vid as vid_2 from explor_kpi_tid_100017 ekt
                        union
                        select ekt.vid as vid_2 from explor_kpi_tid_100018 ekt
                        union
                        select ekt.vid as vid_2 from explor_kpi_tid_100050 ekt
                        )
                        select IFNULL(count(vid_2),0) vehicle from vid_num
                        """
        result_data = (session.execute(text(sql_text)).all())
        result["data"] = result_data[0][0]
        session.commit()
        session.remove()
        result["message"] = ""
        result["code"] = 200
    except PendingRollbackError:
        result["message"] = ""
        result["code"] = 500
        session.rollback()
        session.remove()
    except Exception as e:
        result["message"] = ""
        result["code"] = 500
        print(e)
    return json.dumps(result, ensure_ascii=False)


@dataServiceApp.route('/ai_function_kpi', method=['post'])
def ai_function_kpi():
    """
    [POST] /ai_function_kpi 智驾KPI统计接口,统计一个时间段内的KPI数据，将一次返回整个页面的所有需要数据,开始时间须小于或等于结束时间

    Parameters
    ----------
    start_time : str
        开始时间 2025-01-01
    end_time : str
        结束时间 2025-01-20

    Returns
    -------
    object
        JSON对象
          {
            "total_vehicles": "",
            "mileages": {  # 智驾里程
                "total_mileage": "",  # 总里程数 包含智驾和非智驾
                "total_intelligent_driving_mileage": "",  # 智驾总里程数
                "total_intelligent_driving_mileage_change_rate": "",  # 智驾总里程数增减百分比, 正数为增加，负数为减少
                "detail_intelligent_driving": {
                    "acc": "",  # 自适应巡航
                    "ica": "",  # 智能巡航
                    "hnop": "",  # 高速智驾
                    "unop": "",  # 低速智驾
                }
            },
            "preference": {  # 智驾偏好
                "apa_times_rate_rate": "",  # apa激活次数占比
                "apa_activate_vehicle": "",  # apa激活车数量
                "apa_activate_vehicle_change": "",  # apa激活车辆与上一周期对比变化数量，正数为增加，负数为减少
                "rpa_times_rate_rate": "",  # rpa激活次数占比
                "rpa_activate_vehicle": "",  # rpa激活车数量
                "rpa_activate_vehicle_change": "",  # rpa激活车辆与上一周期对比变化数量，正数为增加，负数为减少
                "vpa_times_rate_rate": "",  # vpa激活次数占比(暂时没有值)
                "vpa_activate_vehicle": "",  # vpa激活车数量(暂时没有值)
                "vpa_activate_vehicle_change": "",  # vpa激活车辆与上一周期对比变化数量，正数为增加，负数为减少(暂时没有值)
            },
            "total_intelligent_driving_vehicles": "",  # 智驾激活车辆总数，包含所有智驾类型
            "total_intelligent_driving_times": "",  # 智驾激活总次数，包含所有智驾类型
            "total_intelligent_driving_vehicles_change": "",  # 智驾激活车辆总数，包含所有智驾类型
            "total_intelligent_driving_times_change": "",  # 智驾激活总次数，包含所有智驾类型
            "total_parking_intelligent_vehicles": "",  # 自动泊车激活车辆数
            "total_parking_intelligent_vehicles_change": "",  # 自动泊车激活车辆数与上一周期对比，正数为增加，负数为减少
            "day_times_parking_intelligent": "",  # 自动泊车单车日均激活次数，
            "day_times_parking_intelligent_day_times_change": "",  # 自动泊车单车日均激活次数与上一周期对比，正数为增加，负数为减少
            "apa_duration_parking_duration": "",  # apa_单次泊车平均时长 秒
            "apa_duration_parking_duration_change": "",  # apa_单次泊车平均时长与上一周期对比， 秒，正数为增加，负数为减少
            "rpa_duration_parking_duration": "",  # rpa_单次泊车平均时长 秒
            "rpa_duration_parking_duration_change": "",  # rpa_单次泊车平均时长与上一周期对比， 秒，正数为增加，负数为减少
            "vpa_duration_parking_duration": "",  # vpa_单次泊车平均时长 秒
            "vpa_duration_parking_duration_change": "",  # vpa_单次泊车平均时长与上一周期对比， 秒，正数为增加，负数为减少
            # 行车功能指标  表数据
            "driving_function_indicators_data": [
                {
                    "function": "",  # 功能名称
                    "vehicles": "",  # 激活车辆数
                    "activate": "",  # 激活次数
                    "avg_mileage": "",  # 单次平均里程
                    "activate_rate": []
                }
            ],
            # 泊车功能指标
            "parking_function_indicators_data": [
                {
                    "function": "",  # 功能名称（VPA，APA，RPA）
                    "avg_duration": "",  # 单次泊车平均时长
                    "activate": "",  # 激活次数
                    "success_rate": "",  # 成功率
                }
            ]
        }
        """
    return DataService_kpi.kpi(request,session)


@dataServiceApp.route('/feedback_insert', method=['POST'])
def feedback_insert():
    """
    [POST]/feedback_insert 插入用户反馈数据

    Parameters
    ----------
    object:json
        {user_name:'用户名',page_position:'问题所在页面',level:'严重级别',description:'描述',attachment:'附件地址',}

    Returns
    -------
    object
        JSON对象
        {
            message:""
            code:200
        }
    """
    params = request.json
    result = {
        "code": 200,
        "message": "新增反馈信息",
        "data": []
    }
    try:
        addData = feedbackDo(
            id=str(uuid.uuid4()),
            user_name=params.get("user_name"),
            page_position=params.get("page_position"),
            level=params.get("level"),
            description=params.get("description"),
            attachment=params.get("attachment"),
            create_time=int(datetime.now().timestamp())
        )
        session.add(addData)
        session.commit()
        session.remove()
        result["message"] = "新增反馈信息成功"
        result["code"] = 200
    except PendingRollbackError:
        result["message"] = "新增反馈信息失败"
        result["code"] = 500
        session.rollback()
        session.remove()
    except Exception as e:
        result["message"] = "新增反馈信息失败"
        result["code"] = 500
        print(e)
    return json.dumps(result, ensure_ascii=False)


@dataServiceApp.route('/feedback_search', method=['GET'])
def feedback_search():
    """
        [GET]/feedback_search 查询用户反馈数据

        Parameters
        ----------
        object:json
            start_time:'2025-01-02',end_time:'2025-01-21',user_name:'用户名',page_position:'问题所在页面',level:'严重级别',description:'描述'

        Returns
        -------
        object
            JSON对象
            {
                message:"",
                code:200,
                "total": 0,
                "data": []
            }
        """
    user_name = request.query.user_name
    page_position = request.query.page_position
    level = request.query.level
    description = request.query.description
    start_time = request.query.start_time
    end_time = request.query.end_time
    ps = request.query.page_size
    cp = request.query.current_page
    page_size = int(ps) or 10
    current_page = int(cp) or 1
    result = {
        "code": 200,
        "message": "查询反馈信息",
        "total": 0,
        "data": []
    }
    try:
        query = session.query(feedbackDo)
        # 添加条件
        if user_name:
            query = query.filter(feedbackDo.user_name.like(f'%{user_name}%'))  # 模糊匹配
        if page_position:
            query = query.filter(feedbackDo.page_position == page_position)  # 精确匹配
        if level:
            query = query.filter(feedbackDo.level == level)  # 精确匹配
        if description:
            query = query.filter(feedbackDo.description.like(f'%{description}%'))  # 模糊匹配
        # 添加时间范围条件
        if start_time:
            query = query.filter(feedbackDo.create_time >= start_time)  # 开始时间
        if end_time:
            query = query.filter(feedbackDo.create_time <= end_time)  # 结束时间

        # 计算符合条件的总条数
        total = query.count()  # 获取总记录数
        # 按 create_time 逆序排列
        query = query.order_by(desc(feedbackDo.create_time))
        # 添加分页
        query = query.limit(page_size).offset((current_page - 1) * page_size)

        result_data = query.all()
        for data in result_data:
            result["data"].append({"id": data.id, "user_name": data.user_name, "page_position": data.page_position,
                                   "level": data.level, "description": data.description, "attachment": data.attachment,
                                   "create_time": timestamp_to_str(data.create_time)})
        session.commit()
        session.remove()
        result["message"] = "查询反馈信息成功"
        result["code"] = 200
        result["total"] = total

    except PendingRollbackError:
        result["message"] = "查询反馈信息失败"
        result["code"] = 500
        session.commit()
        session.remove()
    except Exception as e:
        result["message"] = "查询反馈信息失败"
        result["code"] = 501
        print(e)
    return json.dumps(result, ensure_ascii=False)


@dataServiceApp.route('/get_trigger_id_date', method=['GET'])
def get_trigger_id_date():
    """
    [GET]/get_trigger_id_date 获取所有TriggerId数据

    Parameters
    ----------
    object: null

    Returns
    -------
    object
        JSON对象
        {
            message:"",
            code:200,
            "total": 0,
            "data": []
        }
    """
    result = {
        "code": 200,
        "message": "trigger",
        "data": []
    }
    try:
        result_data = (session.execute(text(
            # 这是之前的根据触发次数统计的方式，现在改为直接按照triggerid大小排序
            # """with filter_td as(
            #     select DISTINCT fc.tid from files_completeness fc where fc.comment ='已忽略的trigger'
            #     ),
            #     trigger_count as(
            #      select brd.trigger_id,count(brd.trigger_id) as count_id
            #      from beta_raw_data brd
            #      where   brd.ota_version like '%OTA5%' and brd.trigger_id not in(select ft.tid from filter_td ft)
            #      GROUP by brd.trigger_id
            #      )
            #     select tiim.trigger_id,tiim.total_type,tiim.sub_type,tiim.retrun_describe,tiim.remarks ,
            #     (CASE WHEN tc.count_id is null then 0 else tc.count_id end) as count_id
            #     from trigger_id_info_mngt tiim left join trigger_count tc on tc.trigger_id = tiim.trigger_id
            #     order by tc.count_id desc"""
            f"""
              select tiim.trigger_id
	                 ,tiim.total_type
	                 ,tiim.sub_type
	                 ,tiim.retrun_describe
	                 ,tiim.remarks
	                 ,tiim.level
	                 ,tiim.is_function
	                 ,tiim.involve_function
	                 ,retrun_describe_EN
                from trigger_id_info_mngt tiim 
               where tiim.is_ignore = 0 
               order by tiim.trigger_id asc ;
            """
        )).all())
        # 找到所有的
        for data in result_data:
            result["data"].append([data[0], data[1], data[2], data[3], data[4], data[5], data[6], data[7], data[8]])
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


#  获取获取所有车辆网速信息
@dataServiceApp.route('/get_speed_date', method=['post'])
def get_speed_date():
    """
    [post]/get_speed_date 获取所有TriggerId数据

    Parameters
    ----------
    start_time:'2025-01-02',end_time:'2025-01-21'
    vehicles:str 车辆数据 逗号隔开

    Returns
    -------
    object
        JSON对象
        {
            message:"",
            code:200,
            "total": 0,
            "data": []
        }
    """
    json_params = request.json
    start_date = json_params["start_date"]
    end_date = json_params["end_date"]
    vehicles = str2Array(json_params["vehicles"])
    vehiclesStr = ""
    vehiclesArray = []
    for vehicle in vehicles:
        if len(vehicle) > 0:
            vehiclesArray.append(vehicle)
    if len(vehiclesArray) > 0:
        in_cnd = "','".join(vehiclesArray)
        vehiclesStr = f" and (right(pas.uuid,12) in ('{in_cnd}'))"
    result = {
        "code": 200,
        "message": "车辆网速",
        "data": {
            "all_data": [],
            "charts_data": [],
            "all_vin": [],
            "all_date": []
        }
    }
    try:
        query = text(f"""
            SELECT   
            DATE_FORMAT(pas.date, '%Y-%m-%d') AS date,
            right(pas.uuid,12) as uuid,
            if(ISNULL(vim.vin),right(pas.uuid,12),vim.vin) as vin,
            if(ISNULL(vim.vin),right(pas.uuid,12),vim.int_vehicle_no) as int_vehicle_no,
            if(ISNULL(vim.vin),'其他',vim.vehicle_tested_by) as vehicle_tested_by,
            round(pas.avg_speed,2) as avg_speed,
            round(pas.std_speed,2) as std_speed,
            pas.sum_file_size,
            pas.sum_cost,
            pas.data_count,
            pas.sum_speed,
            pas.max_speed,
            pas.min_speed,
            pas.sum_file_size/pas.data_count as file_size
            FROM   
               parse_api_speed_agg pas left join vehicle_info_mngt vim on vim.chery_no = pas.uuid 
            where pas.date >= '{start_date}' AND pas.date <= '{end_date}' {vehiclesStr}
            order by 
                date asc
        """)
        result_data = pd.read_sql(query, engine)
        # date          uuid                vin int_vehicle_no vehicle_tested_by  file_size   cost
        # result_data = df.groupby(['date', 'uuid','vin','int_vehicle_no','vehicle_tested_by']).agg(
        #     file_size=('file_size','mean'),
        #     cost=('cost', 'mean'),
        #     cnt=('cost','count')
        # )
        # result_data['speed'] = result_data['file_size'] / result_data['cost']

        result_k_data = result_data.groupby(['date']).agg(
            speed_sum=('sum_speed','sum'),
            speed_min=('min_speed','min'),
            speed_max=('max_speed','max'),
            speed_std=('std_speed','mean'),
            count=('data_count','sum'),
            size_sum=('sum_file_size','sum')
        )

        result_k_data['speed_min'] = round(result_k_data['speed_min'],2)
        result_k_data['speed_max'] = round(result_k_data['speed_max'],2)
        # result_k_data['speed_avg'] = round(result_k_data['speed_avg'],2)
        result_k_data['speed_std'] = round(result_k_data['speed_std'],2)

        all_date = result_data.groupby(['date'])
        all_vin = result_data.groupby(['uuid','vin','int_vehicle_no','vehicle_tested_by'])

        # result_data = result_data.drop(columns=['file_size', 'cost','cnt'])

        #构建返回json
        for row in result_data.itertuples():
            result["data"]["all_data"].append(
                [row.date,row.uuid, row.vin, row.avg_speed, row.int_vehicle_no, row.vehicle_tested_by])
        for row in result_k_data.itertuples():
            result["data"]["charts_data"].append(
                [row.Index, round(float(row.speed_sum / row.count),2), float(row.speed_std), float(row.speed_min), float(row.speed_max), row.count, round(row.size_sum/row.count/1024/1024,2)])

        result["data"]["all_vin"] = [[group_name[0], group_name[1], group_name[2], group_name[3]]for group_name,group_df in list(all_vin)]

        result["data"]["all_date"] = [group_name for group_name, group_df  in list(all_date)]
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


#  获取获取所有车辆信息 携带ota
@dataServiceApp.route('/get_all_vehicle_files_by_date', method=['GET'])
def get_all_vehicle_files_by_date():
    """
    [GET]/get_all_vehicle_files_by_date 根据时间和otaVersion获取车辆文件信息

    Parameters
    ----------
    start_time:'2025-01-02',end_time:'2025-01-21',ota_versions

    Returns
    -------
    object
        JSON对象
        {
            message:"",
            code:200,
            "total": 0,
            "data": [vin,uuid,package_size,current_files,data_ts,int_vehicle_no,vehicle_tested_by]
        }
    """
    start_date = date_str_to_timestamp(request.query.start_date) * 1000
    end_date = date_str_to_timestamp(request.query.end_date) * 1000
    otas = request.query.ota_versions.split(",")
    otasStr = ""
    if len(otas) > 0:
        otasStrArray = []
        for ota in otas:
            otasStrArray.append(f"fc.ota_version like '%{ota}%'")
        otasStr = f" and ({" or ".join(otasStrArray)})"
    result = {
        "code": 200,
        "message": "数据完整性",
        "data": []
    }
    sql = f"""select vim.vin,fc.uuid,fc.package_size,fc.current_files,fc.data_ts,vim.int_vehicle_no,vim.vehicle_tested_by
         from vehicle_info_mngt vim left join files_completeness fc on vim.chery_no = fc.uuid where fc.uuid != '' 
         and fc.data_ts >= {start_date} and fc.data_ts <= {end_date}  {otasStr}
          and (fc.comment = '' or fc.comment = null) """
    try:
        result_data = (session.execute(text(sql))
                       .all())
        # 找到所有的
        for data in result_data:
            result["data"].append([data[0], data[1], data[2], data[3], data[4], data[5], data[6]])
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


#  获取车辆的文件名------------
@dataServiceApp.route('/get_vehicle_files_by_date', method=['GET'])
def get_vehicle_files_by_date():
    """
        [GET]/get_vehicle_files_by_date 获取车辆的文件名

        Parameters
        ----------
        start_date:'2025-01-02',end_date:'2025-01-21',vin

        Returns
        -------
        object
            JSON对象
            {
                message:"",
                code:200,
                "total": 0,
                "data": [vin,data_ts,current_files]
            }
        """
    start_date = date_str_to_timestamp(request.query.date) * 1000
    end_date = start_date + 86400000
    vin_code = request.query.vin
    # 返回数据
    result = {
        "code": 200,
        "message": "数据完整性",
        "data": []
    }
    try:
        result_data = (session.execute(text(f"""
            select vim.vin,fc.data_ts ,fc.current_files from vehicle_info_mngt vim left 
            join files_completeness fc on 
            vim.chery_no = fc.uuid where vim.vin = '{vin_code}' and fc.data_ts >= {start_date} 
            and fc.data_ts < {end_date} and fc.comment = '' """))
                       .all())
        # 找到所有的
        for data in result_data:
            result["data"].append([data[0], data[1], data[2]])
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


@dataServiceApp.route('/get_vin_for_data_integrity', method=['GET'])
def get_vin_for_data_integrity():
    """
    [GET]/get_vin_for_data_integrity 根据时间段信息获取车辆vin，大类，小类

    Parameters
    ----------
    start_date:'2025-01-02',end_date:'2025-01-21',ota_versions

    Returns
    -------
    object
        JSON对象
        {
            message:"",
            code:200,
            "total": 0,
            "data": [vin,vehicle_tested_by,int_vehicle_no]
        }
    """
    start_date = request.query.start_date
    end_date = request.query.end_date
    otas = request.query.ota_versions.split(",")
    otasStr = ""
    if len(otas) > 0:
        otasStrArray = []
        for ota in otas:
            otasStrArray.append(f"fc.ota_version like '%{ota}%'")
        otasStr = f" and ({" or ".join(otasStrArray)})"
    # 返回数据
    result = {
        "code": 200,
        "message": "数据完整性-所有vin",
        "data": []
    }
    try:
        result_data = (session.execute(text(f"""
            with vin_data as (
                select 
                DATE_FORMAT(FROM_UNIXTIME(fc.data_ts/1000),'%Y-%m-%d') as data_temp,
                vim.vin,min(vim.vehicle_tested_by) as vehicle_tested_by,min(vim.int_vehicle_no) as int_vehicle_no
                from files_completeness fc left join vehicle_info_mngt vim on vim.chery_no = fc.uuid
                where fc.ota_version is not null and fc.ota_version <> "" {otasStr}
                group by vim.vin, data_temp
            )
            select vd.vin ,min(vd.vehicle_tested_by) as vehicle_tested_by,min(vd.int_vehicle_no) as int_vehicle_no
            from vin_data vd 
             where data_temp >= '{start_date}' and data_temp <= '{end_date}' 
             group by vd.vin
             order by vehicle_tested_by
            """))
                       .all())
        # 找到所有的
        for data in result_data:
            result["data"].append(data[0])
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


@dataServiceApp.route('/get_ota_for_data_integrity', method=['GET'])
def get_ota_for_data_integrity():
    """
    [GET]/get_ota_for_data_integrity 根据时间段信息获取所有版本

    Parameters
    ----------
    start_date:'2025-01-02',end_date:'2025-01-21'

    Returns
    -------
    object
        JSON对象
        {
            message:"",
            code:200,
            "total": 0,
            "data": [ota]
        }
    """
    start_date = request.query.start_date
    end_date = request.query.end_date
    # 返回数据
    result = {
        "code": 200,
        "message": "数据完整性-所有ota",
        "data": []
    }
    try:
        result_data = (session.execute(text(f"""
        with otas_data as (
            select 
            DATE_FORMAT(FROM_UNIXTIME(fc.data_ts/1000),'%Y-%m-%d') as data_temp,
            SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(fc.ota_version,'-',-2),'_',-1 ),1,17) as ota,
            max(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(fc.ota_version,'-',-2),'_',-1 ),1,4)) as ota_version_type,
            max(SUBSTRING_INDEX(SUBSTRING_INDEX(fc.ota_version,'-',-2),'_',1)) as ota_win_type
            from files_completeness fc
            group by ota,data_temp
        ),
        ota_list as (
        select if((ad.ota is null or ad.ota='' or ad.ota='unknown'),'unknown',ad.ota) ota from otas_data ad 
         where data_temp >= '{start_date}' and data_temp <= '{end_date}' and 
          ((ad.ota_version_type >= 'OTA5' and ad.ota_version_type like '%OTA%' and ad.ota_win_type >= 'WIN2.0') or (ad.ota is null or ad.ota='' or ad.ota='unknown'))
         group by ad.ota
         )
         select ol.ota from ota_list ol group by ol.ota order by ol.ota desc
          """))
                       .all())
        # 找到所有的
        for data in result_data:
            result["data"].append(data[0])
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


@dataServiceApp.route('/get_file_size_data_integrity', method=['post'])
def get_file_size_data_integrity():
    """
    [POST]/get_file_size_data_integrity 获取数据质量页面数据包大小数据

    Parameters
    ----------
    start_date:'2025-01-02'
    end_date:'2025-01-21'
    ota_versions:str
    vins:str
    triggerId:str

    Returns
    -------
    object
        JSON对象
        {
            message:"",
            code:200,
            "total": 0,
            "data": [data_temp,total_size,total_num ]
        }
    """
    return DataService_data_integrity.get_file_size_data_integrity(request,session)


#  数据完整性
@dataServiceApp.route('/data_integrity', method=['post'])
def get_data_integrity():
    """
    [POST]/data_integrity 获取数据质量页面数数据

    Parameters
    ----------
    start_date:'2025-01-02'
    end_date:'2025-01-21'
    ota_versions:str
    vins:str
    triggerId:str

    Returns
    -------
    object
        JSON对象
        {
            message:"",
            code:200,
            "total": 0,
            "data": {
                "daily_rate": [],
                "daily_vehicle_rate": [],
                "vehicle": [],
                "file_size":[]
            }
        }
    """
    return DataService_data_integrity.data_integrity(request,session)


#  数据完整性-trigger维度
@dataServiceApp.route('/data_integrity_hot_map_by_trigger', method=['POST'])
def data_integrity_hot_map_by_trigger():
    """
    [POST]/data_integrity_hot_map_by_trigger 获取数据质量页面数数据 -trigger维度

    Parameters
    ----------
    start_date:'2025-01-02'
    end_date:'2025-01-21'
    ota_versions:str
    vins:str
    triggerId:str

    Returns
    -------
    object
        JSON对象
        {
            message:"",
            code:200,
            "data": [{}]
        }
    """
    return DataService_data_integrity.data_integrity_hot_map_by_trigger(request,session)


@dataServiceApp.route('/data_integrity_hot_map_by_vehicle', method=['POST'])
def data_integrity_hot_map_by_vehicle():
    """
        [POST]/data_integrity_hot_map_by_vehicle 数据质量-车辆统计

        Parameters
        ----------
        start_date:'2025-01-02'
        end_date:'2025-01-21'
        ota_versions:str
        vins:str
        triggerId:str

        Returns
        -------
        object
            JSON对象
            {
                message:"",
                code:200,
                "total": 0,
                "data": []
            }
        """
    return DataService_data_integrity.data_integrity_hot_map_by_vehicle(request,session)


@dataServiceApp.route('/data_integrity_hot_map_by_vehicle_detail', method=['POST'])
def data_integrity_hot_map_by_vehicle_detail():
    """
            [POST]/data_integrity_hot_map_by_vehicle_detail 数据质量-车辆统计

            Parameters
            ----------
            date: 当前单元个对应的日期
            vehicle: 当前单元个对应的车辆uuid，只有一个
            ota_versions:str 多个逗号隔开
            triggerId:str 多个逗号隔开

            Returns
            -------
            object
                JSON对象
                {
                    message:"",
                    code:200,
                    "total": 0,
                    "data": []
                }
            """
    return DataService_data_integrity.data_integrity_hot_map_by_vehicle_detail(request, session)


# trigger总触发次数
@dataServiceApp.route('/trigger_id_count', method=['GET'])
def trigger_id_count():
    """
    [GET]/trigger_id_count trigger总触发次数

    Parameters
    ----------
    start_date:'2025-01-02'
    end_date:'2025-01-21'
    triggerId:str

    Returns
    -------
    object
        JSON对象
        {
            message:"",
            code:200,
            "total": 0,
            "data": [{"trigger_id": , "trigger_id_describe":, "times": }]
        }
    """
    start_date = request.query.start_date
    end_date = request.query.end_date
    ota_versions = request.query.ota_versions.split(",")
    start_date = date_str_to_timestamp(start_date) * 1000
    end_date = date_str_to_timestamp(end_date) * 1000 + 86399000
    trigger_id = request.query.triggerId
    triggerIdList = []
    if len(trigger_id) > 0:
        trigger_ids = trigger_id.split(",")
        for tid in trigger_ids:
            triggerIdList.append(tid)
    # 返回数据
    result = {
        "code": 200,
        "message": "trigger总触发次数",
        "data": []
    }

    try:
        query = session.query(betaRawDataDo.trigger_id, triggerIdInfoMngt.retrun_describe,
                              func.count(betaRawDataDo.uuid)) \
            .outerjoin(triggerIdInfoMngt, triggerIdInfoMngt.trigger_id == betaRawDataDo.trigger_id) \
            .filter(
            and_(betaRawDataDo.data_ts.between(start_date, end_date),
                 func.substring(func.substring_index(func.substring_index(betaRawDataDo.ota_version, "-", -2), "_", -1),
                                1, 17).in_(ota_versions))) \
            .group_by(betaRawDataDo.trigger_id, triggerIdInfoMngt.retrun_describe) \
            .order_by(func.count(betaRawDataDo.uuid).desc())
        if len(triggerIdList) > 0:
            query = query.filter(and_(betaRawDataDo.trigger_id.in_(triggerIdList)))
        result_data = query.all()
        for data in result_data:
            result["data"].append({"trigger_id": data[0], "trigger_id_describe": data[1], "times": data[2]})
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


# 每台车总的触发次数 [{carName:'车辆1’,times:2}]
@dataServiceApp.route('/single_trigger_id_count', method=['GET'])
def single_trigger_id_count():
    """
    [GET]/single_trigger_id_count 每台车总的触发次数

    Parameters
    ----------
    start_date:'2025-01-02'
    end_date:'2025-01-21'
    ota_versions:str
    triggerId:str

    Returns
    -------
    object
        JSON对象
        {
            message:"",
            code:200,
            "total": 0,
            "data": [{"carName": , "vin": , "times": ]
        }
    """
    start_date = request.query.start_date
    end_date = request.query.end_date
    ota_versions = request.query.ota_versions.split(",")
    start_date = date_str_to_timestamp(start_date) * 1000
    end_date = date_str_to_timestamp(end_date) * 1000 + 86399000
    trigger_id = request.query.triggerId
    triggerIdList = []
    if len(trigger_id) > 0:
        trigger_ids = trigger_id.split(",")
        for tid in trigger_ids:
            triggerIdList.append(tid)
    result = {
        "code": 200,
        "message": "每台车总的触发次数",
        "data": []
    }
    try:
        query = session.query(vehicleInfoMngtDo.int_vehicle_no, vehicleInfoMngtDo.vin,
                              func.count(betaRawDataDo.trigger_id)) \
            .outerjoin(vehicleInfoMngtDo, betaRawDataDo.uuid == vehicleInfoMngtDo.chery_no) \
            .filter(
            and_(betaRawDataDo.data_ts.between(start_date, end_date),
                 func.substring(func.substring_index(func.substring_index(betaRawDataDo.ota_version, "-", -2), "_", -1),
                                1, 17).in_(ota_versions))) \
            .group_by(vehicleInfoMngtDo.int_vehicle_no, vehicleInfoMngtDo.vin) \
            .order_by(func.count(betaRawDataDo.trigger_id).desc())
        if len(triggerIdList) > 0:
            query = query.filter(betaRawDataDo.trigger_id.in_(triggerIdList))
        result_data = query.all()
        for data in result_data:
            result["data"].append({"carName": data[0], "vin": data[1], "times": data[2]})
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


# 每天每台车触发次数
@dataServiceApp.route('/daily_single_trigger_id_count', method=['GET'])
def single_daily_trigger_id_count():
    """
    [GET]/daily_single_trigger_id_count 每天每台车触发次数

    Parameters
    ----------
    start_date:'2025-01-02'
    end_date:'2025-01-21'
    ota_versions:str
    triggerId:str

    Returns
    -------
    object
        JSON对象
        {
            message:"",
            code:200,
            "total": 0,
            "data": [{"vehicle":, "vin": , "date": , "times": }]
        }
    """
    start_date = request.query.start_date
    end_date = request.query.end_date
    otas = str2Array(request.query.ota_versions)
    trigger_ids = str2Array(request.query.triggerId)
    triggerIdStr = ""
    tid_list = []
    for tid in trigger_ids:
        if len(tid) > 0:
            tid_list.append(tid)
    if len(tid_list) > 0:
        triggerIdStr = f" and dt.trigger_id in ('{"','".join(tid_list)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and  dt.ota_version in ('{"','".join(otasStrArray)}')"
    result = {
        "code": 200,
        "message": "每天每台车触发次数",
        "data": []
    }
    try:
        # query = session.query(vehicleInfoMngtDo.int_vehicle_no, vehicleInfoMngtDo.vin,
        #                       func.from_unixtime((betaRawDataDo.data_ts / 1000), "%Y-%m-%d"),
        #                       func.count(betaRawDataDo.trigger_id)) \
        #     .outerjoin(vehicleInfoMngtDo, betaRawDataDo.uuid == vehicleInfoMngtDo.chery_no) \
        #     .filter(
        #     and_(and_(betaRawDataDo.ota_version.like("%OTA5%"), betaRawDataDo.data_ts.between(start_date, end_date)),
        #          func.substring(func.substring_index(func.substring_index(betaRawDataDo.ota_version, "-", -2), "_", -1),
        #                         1, 17).in_(ota_version_list))) \
        #     .group_by(vehicleInfoMngtDo.int_vehicle_no, vehicleInfoMngtDo.vin,
        #               func.from_unixtime((betaRawDataDo.data_ts / 1000), "%Y-%m-%d"))
        # if len(triggerIdList) > 0:
        #     query = session.query(vehicleInfoMngtDo.int_vehicle_no, vehicleInfoMngtDo.vin,
        #                           func.from_unixtime((betaRawDataDo.data_ts / 1000), "%Y-%m-%d"),
        #                           func.count(betaRawDataDo.trigger_id)) \
        #         .outerjoin(vehicleInfoMngtDo, betaRawDataDo.uuid == vehicleInfoMngtDo.chery_no) \
        #         .filter(and_(
        #         and_(betaRawDataDo.ota_version.like("%OTA5%"), betaRawDataDo.data_ts.between(start_date, end_date)),
        #         func.substring(
        #             func.substring_index(func.substring_index(betaRawDataDo.ota_version, "-", -2), "_", -1), 1,
        #             17).in_(ota_version_list)
        #         , betaRawDataDo.trigger_id.in_(triggerIdList))) \
        #         .group_by(vehicleInfoMngtDo.int_vehicle_no, vehicleInfoMngtDo.vin,
        #                   func.from_unixtime((betaRawDataDo.data_ts / 1000), "%Y-%m-%d"))
        # result_data = query.all()
        # for data in result_data:
        #     result["data"].append({"vehicle": data[0], "vin": data[1], "date": data[2], "times": data[3]})
        sql_text = f"""
                    with data_temp as(
                    select DATE_FORMAT(FROM_UNIXTIME(brd.data_ts/1000),'%Y-%m-%d') as date_temp,
                    SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(brd.ota_version,'-',-2),'_',-1 ),1,17) as ota_version,
                    SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(brd.ota_version,'-',-2),'_',-1 ),1,4) as ota_version_type,
                    SUBSTRING_INDEX(SUBSTRING_INDEX(brd.ota_version,'-',-2),'_',1) as ota_win_type,
                    right(brd.uuid,12) as uuid_simple,
                    brd.uuid,
                    brd.trigger_id ,
                    vim.vin,
                    vim.vehicle_tested_by ,
                    vim.int_vehicle_no
                    from beta_raw_data brd left join vehicle_info_mngt vim on brd.uuid = vim.chery_no
                    )
                    select dt.date_temp, count(dt.trigger_id ) times, max(dt.uuid_simple) uuid, max(dt.ota_version) as ota_version,
                    (case when max(dt.vin) is null or max(dt.vin) = '' then max(dt.uuid_simple) else max(dt.vin) end) vin,
                    (case when max(dt.vehicle_tested_by) is null or max(dt.vehicle_tested_by) = '' then '其他' else max(dt.vehicle_tested_by) end) user_name,
                    (case when max(dt.int_vehicle_no) is null or max(dt.int_vehicle_no) = '' then max(dt.uuid_simple) else max(dt.int_vehicle_no) end) class_name
                    from data_temp dt
                    where dt.ota_version_type >= 'OTA5'
                     and dt.ota_version_type like '%OTA%'
                    and dt.ota_win_type >= 'WIN2.0' 
                    and dt.date_temp >= '{start_date}' and dt.date_temp <= '{end_date}' {triggerIdStr} {otasStr}
                    group by dt.date_temp
                    order by dt.date_temp
                """
        result_data = (session.execute(text(sql_text)).all())
        for data in result_data:
            result["data"].append(
                {"vehicle": data[6],
                 "date": data[0],
                 "times": data[1],
                 "vin": data[4],
                 "uuid": data[2]})
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


# 最新一天的触发次数Top5
@dataServiceApp.route('/latest_day_trigger_id_count', method=['GET'])
def latest_day_trigger_id_count():
    """
    [GET]/latest_day_trigger_id_count 最新一天的触发次数Top5

    Parameters
    ----------
    start_date:'2025-01-02'
    end_date:'2025-01-21'
    ota_versions:str
    triggerId:str

    Returns
    -------
    object
        JSON对象
        {
            message:"",
            code:200,
            "total": 0,
            "data": [{"name": , "vin": , "version": , "count": }]
        }
    """
    start_date = request.query.start_date
    end_date = request.query.end_date
    start_date = date_str_to_timestamp(start_date) * 1000
    end_date = date_str_to_timestamp(end_date) * 1000 + 86399000
    triggerIdList = str2Array(request.query.triggerId)
    ota_version_list = str2Array(request.query.ota_versions)
    result = {
        "code": 200,
        "message": "最新一天的触发次数top5",
        "data": []
    }
    parse_data = {}
    try:
        query = session.query(vehicleInfoMngtDo.int_vehicle_no, vehicleInfoMngtDo.vin, func.substring(
            func.substring_index(func.substring_index(betaRawDataDo.ota_version, "-", -2), "_", -1), 1, 17),
                              func.count(betaRawDataDo.trigger_id)) \
            .outerjoin(vehicleInfoMngtDo, betaRawDataDo.uuid == vehicleInfoMngtDo.chery_no) \
            .filter(
            and_(betaRawDataDo.data_ts.between(start_date, end_date),
                 func.substring(func.substring_index(func.substring_index(betaRawDataDo.ota_version, "-", -2), "_", -1),
                                1, 17).in_(ota_version_list))) \
            .group_by(vehicleInfoMngtDo.int_vehicle_no, vehicleInfoMngtDo.vin, func.substring(
            func.substring_index(func.substring_index(betaRawDataDo.ota_version, "-", -2), "_", -1), 1, 17)) \
            .order_by(func.count(betaRawDataDo.trigger_id).desc()) \
            .limit(5)
        if len(triggerIdList) > 0:
            query = query.filter(betaRawDataDo.trigger_id.in_(triggerIdList))
        result_data = query.all()
        for data in result_data:
            result["data"].append({"name": data[0], "vin": data[1], "version": data[2], "count": data[3]})
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


# 根据vin获取每台车的触发次数
@dataServiceApp.route('/single_trigger_id_count_for_vin', method=['GET'])
def single_trigger_id_count_for_vin():
    """
    [GET]/single_trigger_id_count_for_vin 根据vin获取每台车的触发次数

    Parameters
    ----------
    start_date:'2025-01-02'
    end_date:'2025-01-21'
    ota_versions:str
    triggerId:str
    vin:str

    Returns
    -------
    object
        JSON对象
        {
            message:"",
            code:200,
            "total": 0,
            "data": [{"vehicle":, "trigger_id":, "trigger_id_describe": , "date":,"times": }]
        }
    """
    start_date = request.query.start_date
    end_date = request.query.end_date
    vin = request.query.vin
    start_date = date_str_to_timestamp(start_date) * 1000
    end_date = date_str_to_timestamp(end_date) * 1000 + 86399000
    trigger_id = request.query.triggerId
    triggerIdList = []
    if len(trigger_id) > 0:
        trigger_ids = trigger_id.split(",")
        for tid in trigger_ids:
            triggerIdList.append(tid)
    result = {
        "code": 200,
        "message": "根据uuid获取每台车的触发次数",
        "data": []
    }
    try:
        query = session.query(vehicleInfoMngtDo.int_vehicle_no, betaRawDataDo.trigger_id,
                              triggerIdInfoMngt.retrun_describe,
                              func.from_unixtime((betaRawDataDo.data_ts / 1000), "%Y-%m-%d"),
                              func.count(betaRawDataDo.trigger_id)) \
            .outerjoin(triggerIdInfoMngt, betaRawDataDo.trigger_id == triggerIdInfoMngt.trigger_id) \
            .outerjoin(vehicleInfoMngtDo, vehicleInfoMngtDo.chery_no == betaRawDataDo.uuid) \
            .filter(
            and_(betaRawDataDo.data_ts.between(start_date, end_date),
                 vehicleInfoMngtDo.vin == vin)) \
            .group_by(vehicleInfoMngtDo.int_vehicle_no, betaRawDataDo.trigger_id, triggerIdInfoMngt.retrun_describe,
                      func.from_unixtime((betaRawDataDo.data_ts / 1000), "%Y-%m-%d")) \
            .order_by(func.count(betaRawDataDo.trigger_id).desc())
        if len(triggerIdList) > 0:
            query = query.filter(betaRawDataDo.trigger_id.in_(triggerIdList))
        result_data = query.all()
        for data in result_data:
            result["data"].append(
                {"vehicle": data[0], "trigger_id": data[1], "trigger_id_describe": data[2], "date": data[3],
                 "times": data[4]})
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


# 版本号热力图
@dataServiceApp.route('/daily_single_version', method=['GET'])
def daily_single_version():
    """
        [GET]/daily_single_version 版本号热力图

        Parameters
        ----------
        start_date:'2025-01-02'
        end_date:'2025-01-21'
        ota_versions:str
        triggerId:str

        Returns
        -------
        object
            JSON对象
            {
                message:"",
                code:200,
                "total": 0,
                "data": [{"vehicle": , "date": , "version": ,"version_index": ,"vin":}]
            }
        """
    start_date = request.query.start_date
    end_date = request.query.end_date
    otas = str2Array(request.query.ota_versions)
    # 版本触发详情不受到trigger设置影响
    trigger_ids = [] # str2Array(request.query.triggerId)
    triggerIdStr = ""
    tid_list = []
    for tid in trigger_ids:
        if len(tid) > 0:
            tid_list.append(tid)
    if len(tid_list) > 0:
        triggerIdStr = f" and dt.trigger_id in ('{"','".join(tid_list)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and  dt.ota_version in ('{"','".join(otasStrArray)}')"
    result = {
        "code": 200,
        "message": "版本号热力图",
        "data": []
    }
    # ota_version_dict = {}
    # vin_dict = {}
    # driver_name_set = set()
    # ota_version_set = set()
    # date_set = set()
    # parse_data = {}
    try:
        # query = session.query(vehicleInfoMngtDo.int_vehicle_no,
        #                       func.from_unixtime((betaRawDataDo.data_ts / 1000), "%Y-%m-%d"),
        #                       betaRawDataDo.ota_version, vehicleInfoMngtDo.vin) \
        #     .outerjoin(vehicleInfoMngtDo, betaRawDataDo.uuid == vehicleInfoMngtDo.chery_no) \
        #     .filter(
        #     and_(betaRawDataDo.ota_version.like("%OTA5%"), betaRawDataDo.data_ts.between(start_date, end_date), )) \
        #     .group_by(vehicleInfoMngtDo.int_vehicle_no, func.from_unixtime((betaRawDataDo.data_ts / 1000), "%Y-%m-%d"),
        #               betaRawDataDo.ota_version, vehicleInfoMngtDo.vin)
        #
        # if len(triggerIdList) > 0:
        #     query = query.filter(betaRawDataDo.trigger_id.in_(triggerIdList))
        # if len(ota_version_list) > 0:
        #     query = query.filter(and_(func.substring(
        #         func.substring_index(func.substring_index(betaRawDataDo.ota_version, "-", -2), "_", -1),
        #         1, 17).in_(ota_version_list)))
        # result_data = query.all()
        # for data in result_data:
        #     ota_version_set.add(data[2].split("_")[-1][:-3])
        #     driver_name_set.add(data[0])
        #     date_set.add(data[1])
        #     vin_dict[data[0]] = data[3]
        #     ota_version_dict[f"{data[0]},{data[1]}"] = data[2]
        # # print(list(sorted(ota_version_set)).index("OTA5-202501032018"))
        # date_set_sorted = sorted(list(date_set))
        # driver_name_set_sorted = sorted(list(driver_name_set), reverse=True)
        # for driver_name in driver_name_set_sorted:
        #     for date in date_set_sorted:
        #         if not f"{driver_name}" in parse_data:
        #             if f"{driver_name},{date}" in ota_version_dict:
        #                 parse_data[driver_name] = [{date: ota_version_dict[f"{driver_name},{date}"]}]
        #             else:
        #                 parse_data[driver_name] = [{date: "notdata"}]
        #         else:
        #             if f"{driver_name},{date}" in ota_version_dict:
        #                 parse_data[driver_name].append({date: ota_version_dict[f"{driver_name},{date}"]})
        #             else:
        #                 parse_data[driver_name].append({date: "notdata"})
        # # 向前补全版本号
        # for key in parse_data:
        #     not_data_cnt = 0
        #     ota_version = ""
        #     flag = False
        #     for val in parse_data[key]:
        #         for k, v in val.items():
        #             if v == "notdata":
        #                 not_data_cnt += 1
        #             else:
        #                 ota_version = v
        #                 flag = True
        #                 break
        #         if flag:
        #             break
        #     if not_data_cnt != 0:
        #         if not_data_cnt - 1 == 0:
        #             parse_data[key][0] = {list(parse_data[key][0].keys())[0]: ota_version}
        #         else:
        #             for i in range(not_data_cnt - 1):
        #                 parse_data[key][i] = {list(parse_data[key][i].keys())[0]: ota_version}
        # # 向后补全版本号
        # for key in parse_data:
        #     not_data_cnt = 0
        #     ota_version = ""
        #     flag = False
        #     for data_index in range(len(parse_data[key]) - 1):
        #         if parse_data[key][data_index + 1][list(parse_data[key][data_index + 1].keys())[0]] == "notdata":
        #             parse_data[key][data_index + 1][list(parse_data[key][data_index + 1].keys())[0]] = \
        #                 parse_data[key][data_index][list(parse_data[key][data_index].keys())[0]]
        #
        # # 重构返回的json
        # ota_version_set_sorted = list(sorted(ota_version_set))  # .index("OTA5-202501032018")
        # for key in parse_data:
        #     for val in parse_data[key]:
        #         result["data"].append(
        #             {"vehicle": key, "date": list(val.keys())[0], "version": list(val.values())[0].split("_")[-1][:-3],
        #              "version_index": ota_version_set_sorted.index(list(val.values())[0].split("_")[-1][:-3]),
        #              "vin": vin_dict[key]})
        sql_text = f"""
            with data_temp as(
            select DATE_FORMAT(FROM_UNIXTIME(brd.data_ts/1000),'%Y-%m-%d') as date_temp,
            SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(brd.ota_version,'-',-2),'_',-1 ),1,17) as ota_version,
            SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(brd.ota_version,'-',-2),'_',-1 ),1,4) as ota_version_type,
            SUBSTRING_INDEX(SUBSTRING_INDEX(brd.ota_version,'-',-2),'_',1) as ota_win_type,
            right(brd.uuid,12) as uuid_simple,
            brd.uuid,
            brd.trigger_id ,
            vim.vin,
            vim.vehicle_tested_by ,
            vim.int_vehicle_no
             from beta_raw_data brd left join vehicle_info_mngt vim on brd.uuid = vim.chery_no
            ),
            ota_index as(
            select (ROW_NUMBER() OVER (ORDER BY dt.ota_version))-1 AS row_num,dt.ota_version from data_temp dt
            where dt.ota_version_type >= 'OTA5' 
             and dt.ota_version_type like '%OTA%'
             and dt.ota_win_type >= 'WIN2.0' 
            and dt.date_temp >= '{start_date}' and dt.date_temp <= '{end_date}' {triggerIdStr} {otasStr}
             group by dt.ota_version
             order by dt.ota_version
            )
            select  dt.date_temp,dt.uuid_simple , max(dt.ota_version) as ota_version,
            (case when max(dt.vin) is null or max(dt.vin) = '' then max(dt.uuid_simple) else max(dt.vin) end) vin,
            (case when max(dt.vehicle_tested_by) is null or max(dt.vehicle_tested_by) = '' then '其他' else max(dt.vehicle_tested_by) end) class_name,
            (case when max(dt.int_vehicle_no) is null or max(dt.int_vehicle_no) = '' then max(dt.uuid_simple) else max(dt.int_vehicle_no) end) user_name,
            (select oi.row_num from ota_index oi where oi.ota_version = max(dt.ota_version)) as ota_index
            from data_temp dt  
            where dt.ota_version_type >= 'OTA5'
            and dt.ota_version_type like '%OTA%'
            and dt.ota_win_type >= 'WIN2.0' 
            and dt.date_temp >= '{start_date}' and dt.date_temp <= '{end_date}' {triggerIdStr} {otasStr}
            group by dt.date_temp,dt.uuid_simple
            order by dt.date_temp
        """
        result_data = (session.execute(text(sql_text)).all())
        for data in result_data:
            result["data"].append(
                {"vehicle": data[5], "date": data[0], "version": data[2],
                 "version_index": data[6],
                 "vin": data[3], "uuid": data[1]})
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


# 文件完整性统计
@dataServiceApp.route('/files_completeness_statistics', method=['GET'])
def files_completeness_statistics():
    """
    [GET]/files_completeness_statistics 文件完整性统计

    Parameters
    ----------
    start_date:'2025-01-02'
    end_date:'2025-01-21'

    Returns
    -------
    object
        JSON对象
        {
            message:"",
            code:200,
            "total": 0,
            "data": [{"name":, "value":}]
        }
    """
    start_date = request.query.start_date
    end_date = request.query.end_date
    start_date = date_str_to_timestamp(start_date) * 1000
    end_date = date_str_to_timestamp(end_date) * 1000 + 86399000
    result = {
        "code": 200,
        "message": "文件完整性统计",
        "data": []
    }
    try:
        filesCompleteness_subquery = session.query(filesCompleteness.file_status, filesCompleteness.data_ts,
                                                   func.count(filesCompleteness.data_ts).label("cnt")) \
            .filter(
            filesCompleteness.data_ts.between(start_date, end_date)) \
            .group_by(filesCompleteness.file_status, filesCompleteness.data_ts) \
            .subquery()
        result_data = session.query(filesCompleteness_subquery.c.file_status,
                                    func.count(filesCompleteness_subquery.c.data_ts)) \
            .group_by(filesCompleteness_subquery.c.file_status).all()
        for data in result_data:
            result["data"].append({"name": data[0], "value": data[1]})
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


# 缺失类型统计
@dataServiceApp.route('/files_missing_type_statistics', method=['GET'])
def files_missing_type_statistics():
    """
    [GET]/files_missing_type_statistics 缺失类型统计

    Parameters
    ----------
    start_date:'2025-01-02'
    end_date:'2025-01-21'

    Returns
    -------
    object
        JSON对象
        {
            message:"",
            code:200,
            "total": 0,
            "data": [{"name":, "value":}]
        }
    """
    start_date = request.query.start_date
    end_date = request.query.end_date
    start_date = date_str_to_timestamp(start_date) * 1000
    end_date = date_str_to_timestamp(end_date) * 1000 + 86399000
    result = {
        "code": 200,
        "message": "缺失类型统计",
        "data": []
    }
    try:
        result_data = session.query(filesCompleteness.missing, func.count(filesCompleteness.data_ts).label("cnt")) \
            .filter(
            filesCompleteness.data_ts.between(start_date, end_date)) \
            .group_by(filesCompleteness.missing) \
            .all()

        for data in result_data:
            result["data"].append({"name": data[0], "value": data[1]})
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


# 版本信息接口
@dataServiceApp.route('/ota_versions', method=['GET'])
def ota_versions():
    """
    [GET]/ota_versions 版本信息接口

    Parameters
    ----------
    start_date:'2025-01-02'
    end_date:'2025-01-21'
    default_check_flag:bool 是否仅限发布版本

    Returns
    -------
    object
        JSON对象
        {
            message:"",
            code:200,
            "total": 0,
            "data": {"ota_version":[]}
        }
    """
    start_date = request.query.start_date
    end_date = request.query.end_date
    default_check_flag = request.query.default_check_flag
    # 返回数据
    result = {
        "code": 200,
        "message": "数据完整性-所有ota",
        "data": {"ota_version":[]}
    }
    try:
        sql_ = f"""
            select 
                max(ovim.ota_ver_stamp),ovim.ota_version
                from ota_version_info_mngt ovim 
                where 
                ovim.date>='{start_date}' and ovim.date<='{end_date}' {'and default_check_flag=1' if default_check_flag == '1' else ''}
                and ((lower(ovim.operate_sys) >='win2.0' and lower(ovim.ota_ver) >= 'ota5') or ovim.ota_ver_stamp = '未知版本')
                group by ovim.ota_version
                order by ovim.ota_version
              """
        result_data = (session.execute(text(sql_)).all())
        # 找到所有的
        for data in result_data:
            result["data"]["ota_version"].append({
                "ota_version_lable":data[0],
                "ota_version": data[1],
            })
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


# 车辆列表接口
@dataServiceApp.route('/vehicle_list', method=['POST'])
def vehicle_list():
    """
    [POST]/vehicle_list 车辆列表接口

    Parameters
    ----------
    start_date:'2025-01-02'
    end_date:'2025-01-21'
    ota_versions:str 多个

    Returns
    -------
    object
        JSON对象
        {
            message:"",
            code:200,
            "total": 0,
            "data": [{"children":[{"level": 2 ,
                                   "name": "856EC803B99B",
                                   "uuid": "856EC803B99B",
                                   "vin": "856EC803B99B"
                                  },.....
                                 ],
                      "level":1,
                      "name": "其他", 
                      "uuid":  "其他", 
                      "vin":  "其他" ,
                      "engname":  "List_vehiclelist_Other"
                     },.....
                    ]
        }
    """
    json_params = request.json
    start_date = json_params["start_date"]
    end_date = json_params["end_date"]
    ota_version_list = json_params["ota_versions"]
    ota_version_sql = ""
    if len(ota_version_list) > 0:
        ota_version_sql = f"and brd.ota_version in ({ota_version_list})"
    result = {
        "code": 200,
        "message": "车辆列表",
        "data": []
    }
    parse_data = {}
    try:
        sql_=f"""
        select concat(t.class_name,'=',t1.vehicle_tested_by_engname) as class_name
			   ,t.user_name
			   ,t.vin
			   ,t.uuid
		  from (select ifnull(max(vim.vehicle_tested_by),'其他') class_name
					   ,max(vim.int_vehicle_no) user_name
					   ,max(vim.vin) as vin
					   ,right(brd.uuid,12) uuid  
				from beta_raw_data brd 
				left join vehicle_info_mngt vim  on vim.chery_no = brd.uuid
				where 1=1 {ota_version_sql}
				and DATE_FORMAT(FROM_UNIXTIME(brd.data_ts/1000),'%Y-%m-%d') >= '{start_date}'
				and DATE_FORMAT(FROM_UNIXTIME(brd.data_ts/1000),'%Y-%m-%d') <= '{end_date}'
				group by brd.uuid
			   ) t
		  left join vehicle_info_mngt_name t1
			on t.class_name = t1.vehicle_tested_by ;
        """
        result_data = ((session.execute(text(sql_))).all())
        for data in result_data:
            if not data[0] in parse_data:
                parse_data[data[0]] = [{"name": data[1] if data[1] else data[3][-12:],
                                        "vin": data[2] if data[2] else data[3][-12:],
                                        "uuid": data[3][-12:],
                                        "level":2,}]
            else:
                parse_data[data[0]].append({"name": data[1] if data[1] else data[3][-12:],
                                            "vin": data[2] if data[2] else data[3][-12:],
                                            "uuid": data[3][-12:],
                                            "level":2,})
        result["data"] = [{'vin':k.split('=')[0],'uuid':k.split('=')[0],"name": k.split('=')[0],"engname": k.split('=')[1], "children": v,"level":1,} for k, v in parse_data.items()]
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


# trigger_id 类型列表接口
@dataServiceApp.route('/trigger_id_list', method=['GET'])
def trigger_id_list():
    """
    [GET]/trigger_id_list   trigger_id类型列表接口

    Parameters
    ----------
    page:int
    page_size:int

    Returns
    -------
    object
        JSON对象
        {
            message:"",
            code:200,
            "total": 0,
            "data": {
            "count": 0,
            "trigger_id_list": [{"trigger_name":, "trigger_id": , "trigger_remarks": }]
            }
        }
    """
    page = int(request.query.page)
    page_size = int(request.query.page_size)
    result = {
        "code": 200,
        "message": "trigger_id列表",
        "data": {
            "count": 0,
            "trigger_id_list": []
        }
    }
    parse_data = {}
    try:
        result_data_count = session.query(func.count(triggerIdInfoMngt.trigger_id)) \
            .all()
        result_data = session.query(triggerIdInfoMngt.retrun_describe, triggerIdInfoMngt.trigger_id,
                                    triggerIdInfoMngt.remarks) \
            .slice((page - 1) * page_size, page * page_size) \
            .all()
        result["data"]["count"] = result_data_count[0][0]
        for data in result_data:
            result["data"]["trigger_id_list"].append(
                {"trigger_name": data[0], "trigger_id": data[1], "trigger_remarks": data[2]})
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


@dataServiceApp.route('/homepage', method=['post'])
def homepage():
    return DataService_homepage.homepage(request,session)


def str2Array(string_, spilt_str=","):
    resList = []
    if string_ and len(string_) > 0:
        temp_list = string_.split(spilt_str)
        for temp in temp_list:
            if len(temp) > 0:
                resList.append(temp)
    return resList
# if __name__ == '__main__':
#     import sys
#
#     paths = sys.path
#     for path in paths:
#         print(path)





@dataServiceApp.route('/pandect_vin_by_date', method=['POST'])
def pandect_vin_by_date():
    """
        [POST] /pandect_vin_by_date 单车触发详情-总览
                还需要加x，y轴统计

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
            ota版本号，逗号隔开
        trigger_id: str
            triggerId 多个用逗号隔开
        vehicles: str
            车辆uuid后12位，多个用逗号隔开
        Returns
        -------
        object
            JSON 数组
              [{
                    date:2025-01-01,
                    uuid_gs:24,
                    tid_gs:56
                }]
    """
    json_params = request.json
    start_date = json_params["start_date"]
    end_date = json_params["end_date"]
    vehicles = str2Array(json_params["vehicles"])
    otas = str2Array(json_params["ota_versions"])
    trigger_ids = str2Array(json_params["triggerId"])

    triggerIdStr = ""
    tid_list = []
    for tid in trigger_ids:
        if len(tid) > 0:
            tid_list.append(tid)
    if len(tid_list) > 0:
        triggerIdStr = f" and dt.trigger_id in ('{"','".join(tid_list)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and  dt.ota_version in ('{"','".join(otasStrArray)}')"

    vehiclesStr = ""
    vehiclesArray = []
    for vehicle in vehicles:
        if len(vehicle) > 0:
            vehiclesArray.append(vehicle)
    if len(vehiclesArray) > 0:
        in_cnd = "','".join(vehiclesArray)
        vehiclesStr = f" and (dt.uuid_simple in ('{in_cnd}') or dt.vin in ('{in_cnd}') or dt.int_vehicle_no in ('{in_cnd}'))"
    result = {
        "code": 200,
        "message": "车辆触发详情-总览",
        "data": []
    }
    sql_ = f"""
              with data_info as(
		select date_temp,trigger_id,uuid_simple,count(1) as times
		  from (select DATE_FORMAT(FROM_UNIXTIME(brd.data_ts/1000),'%Y-%m-%d') as date_temp,
						brd.ota_version as ota_version,
						right(brd.uuid,12) as uuid_simple,
						brd.uuid,
						brd.trigger_id ,
						vim.vin,
						vim.vehicle_tested_by ,
						vim.int_vehicle_no
						from (select data_ts
									 ,ota_version
									 ,uuid
									 ,trigger_id
								from beta_raw_data dt
							   where EXISTS (select 1
											   from trigger_id_info_mngt tiim
											  where dt.trigger_id = tiim.trigger_id
												and tiim.is_ignore = 0  
											)
							{triggerIdStr} {otasStr}      				     
							 ) brd 
						left join 
							 (select vin
									 ,vehicle_tested_by
									 ,int_vehicle_no
									 ,chery_no
								from vehicle_info_mngt 
							 ) vim 
						  on brd.uuid = vim.chery_no
				) dt
		  where dt.date_temp >= '{start_date}' 
			and dt.date_temp <= '{end_date}'    {vehiclesStr}
		  group by date_temp,trigger_id,uuid_simple
		  order by date_temp   
        )
        select a.date_temp,a.uuid_gs,b.tid_gs
          from (select date_temp,count(distinct uuid_simple) as uuid_gs
			      from data_info
			     group by date_temp
               ) a
          left join 
               (select date_temp,sum(times) as tid_gs
			      from data_info
			     group by date_temp
               ) b 
            on a.date_temp = b.date_temp;
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "date": data[0],
                    "uuid_gs": data[1],
                    "tid_gs": int(data[2]),
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)





@dataServiceApp.route('/max_brd_date_temp', method=['POST'])
def max_brd_date_temp():
    """
        [POST] /max_brd_date_temp brd表最大时间
                还需要加x，y轴统计

        Parameters
        ----------
        无

        Returns
        -------
        object
            JSON 数组
              [{
                    date:2025-01-01
                }]
    """
    result = {
        "code": 200,
        "message": "brd表最大时间",
        "data": ""
    }
    sql_ = f"""
        select max(date_temp) as max_brd_date_temp
		  from (select DATE_FORMAT(FROM_UNIXTIME(brd.data_ts/1000),'%Y-%m-%d') as date_temp
		          from beta_raw_data brd
		       ) t ;
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "date": data[0]
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)







@dataServiceApp.route('/vin_ota_by_date', method=['POST'])
def vin_ota_by_date():
    """
        [POST] /vin_ota_by_date 版本触发详情-车辆版本分布图


        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
            ota版本号，逗号隔开


        Returns
        -------
        object
            JSON 数组
              [{
                    date:'2025-04-05',
                    ota_ver_stamp:OTA5-202502231204 ,
                    ver_cts: 60 
                }]
    """
    json_params = request.json
    start_date = json_params["start_date"]
    end_date = json_params["end_date"]
    otas = str2Array(json_params["ota_versions"])

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and  dt.ota_version in ('{"','".join(otasStrArray)}')"

    result = {
        "code": 200,
        "message": "版本触发详情，车辆版本分布图",
        "data": []
    }
    sql_ = f"""
        select date_temp,ota_ver_stamp,count(distinct uuid_simple) as ver_cts
		  from (select brd.date_temp
					   ,brd.ota_version
					   ,brd.uuid_simple
					   ,ifnull(default_check_flag,0) as default_check_flag
					   ,case when ovi.default_check_flag = 1 then ovi.ota_ver_stamp
					         when ovi.default_check_flag = 1 then ovi.ota_ver_stamp
					         else 'Test Version'
                        end as ota_ver_stamp
				  from (select date_temp
							   ,ota_version
							   ,uuid_simple
						  from (select DATE_FORMAT(FROM_UNIXTIME(dt.data_ts/1000),'%Y-%m-%d') as date_temp,
									   dt.ota_version as ota_version,
									   right(dt.uuid,12) as uuid_simple,
									   dt.uuid,
									   dt.trigger_id
								  from beta_raw_data dt
                                 where 1=1 
							     {otasStr}
							   ) a
						 where a.date_temp >= '{start_date}' 
						   and a.date_temp <= '{end_date}'  	   
					   ) brd
				  left join 
                       (select ota_version
				               ,default_check_flag
				               ,ota_ver_stamp
				          from ota_version_info_mngt 
						 where date >= '{start_date}' 
						   and date <= '{end_date}'	
					   ) ovi
					on brd.ota_version = ovi.ota_version
			   ) t 
         group by date_temp,ota_ver_stamp  
         order by ota_ver_stamp,date_temp ;   
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "date": data[0],
                    "ota_ver_stamp": data[1],
                    "ver_cts": data[2],
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)







@dataServiceApp.route('/trigger_by_uuid_count', method=['POST'])
def trigger_by_uuid_count():
    """
        [POST] /trigger_by_uuid_count 车辆触发详情-触发排行-单车触发次数统计

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
            ota版本号，逗号隔开
        trigger_id: str
            triggerId 多个用逗号隔开
        vehicles: str
            车辆uuid后12位，多个用逗号隔开
        Returns
        -------
        object
            JSON 数组
              [{
                    uuid:F765CE0FB79A,
                    vin:             ,
                    user_name:       ,
                    cnt:56
                }]
    """
    json_params = request.json
    start_date = json_params["start_date"]
    end_date = json_params["end_date"]
    vehicles = str2Array(json_params["vehicles"])
    otas = str2Array(json_params["ota_versions"])
    trigger_ids = str2Array(json_params["triggerId"])

    triggerIdStr = ""
    tid_list = []
    for tid in trigger_ids:
        if len(tid) > 0:
            tid_list.append(tid)
    if len(tid_list) > 0:
        triggerIdStr = f" and dt.trigger_id in ('{"','".join(tid_list)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and  dt.ota_version in ('{"','".join(otasStrArray)}')"

    vehiclesStr = ""
    vehiclesArray = []
    for vehicle in vehicles:
        if len(vehicle) > 0:
            vehiclesArray.append(vehicle)
    if len(vehiclesArray) > 0:
        in_cnd = "','".join(vehiclesArray)
        vehiclesStr = f" and (dt.uuid_simple in ('{in_cnd}') or dt.vin in ('{in_cnd}') or dt.int_vehicle_no in ('{in_cnd}'))"
    result = {
        "code": 200,
        "message": "车辆触发详情-触发排行-单车触发次数统计",
        "data": []
    }
    sql_ = f"""
 	select uuid
		   ,case when vin is null or user_name = '' then uuid else vin end vin 
		   ,case when user_name is null or user_name = '' then uuid else user_name end user_name 
		   ,cnt
	  from ( select uuid_simple as uuid,max(vin) as vin,max(int_vehicle_no) as user_name
					,count(trigger_id) as cnt
			   from (select DATE_FORMAT(FROM_UNIXTIME(brd.data_ts/1000),'%Y-%m-%d') as date_temp,
							brd.ota_version as ota_version,
							right(brd.uuid,12) as uuid_simple,
							brd.uuid,
							brd.trigger_id ,
							vim.vin,
							vim.vehicle_tested_by ,
							vim.int_vehicle_no
							from (select data_ts
										 ,ota_version
										 ,uuid
										 ,trigger_id
									from beta_raw_data dt
								   where EXISTS (select 1
												   from trigger_id_info_mngt tiim
												  where dt.trigger_id = tiim.trigger_id
													and tiim.is_ignore = 0  
												)
								{triggerIdStr} {otasStr}
								 ) brd 
							left join 
								 (select vin 
										 ,vehicle_tested_by
										 ,int_vehicle_no
										 ,chery_no
									from vehicle_info_mngt 
								 ) vim 
							  on brd.uuid = vim.chery_no					
					) dt
			  where dt.date_temp >= '{start_date}' 
				and dt.date_temp <= '{end_date}' 
					{vehiclesStr}
			  group by uuid_simple
		   ) t;
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "uuid": data[0],
                    "vin": data[1],
                    "user_name": data[2],
                    "cnt": data[3],
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)






@dataServiceApp.route('/trigger_by_tid_count', method=['POST'])
def trigger_by_tid_count():
    """
        [POST] /trigger_by_tid_count 车辆触发详情-触发排行-tid触发次数统计  

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
            ota版本号，逗号隔开
        trigger_id: str
            triggerId 多个用逗号隔开
        vehicles: str
            车辆uuid后12位，多个用逗号隔开
        Returns
        -------
        object
            JSON 数组
              [{
                    tid:140003,
                    cnt:7
                }]
    """
    json_params = request.json
    start_date = json_params["start_date"]
    end_date = json_params["end_date"]
    vehicles = str2Array(json_params["vehicles"])
    otas = str2Array(json_params["ota_versions"])
    trigger_ids = str2Array(json_params["triggerId"])

    triggerIdStr = ""
    tid_list = []
    for tid in trigger_ids:
        if len(tid) > 0:
            tid_list.append(tid)
    if len(tid_list) > 0:
        triggerIdStr = f" and dt.trigger_id in ('{"','".join(tid_list)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and  dt.ota_version in ('{"','".join(otasStrArray)}')"

    vehiclesStr = ""
    vehiclesArray = []
    for vehicle in vehicles:
        if len(vehicle) > 0:
            vehiclesArray.append(vehicle)
    if len(vehiclesArray) > 0:
        in_cnd = "','".join(vehiclesArray)
        vehiclesStr = f" and (dt.uuid_simple in ('{in_cnd}') or dt.vin in ('{in_cnd}') or dt.int_vehicle_no in ('{in_cnd}'))"
    result = {
        "code": 200,
        "message": "车辆触发详情-触发排行-tid触发次数统计",
        "data": []
    }
    sql_ = f"""
         select trigger_id as tid 
		        ,count(trigger_id) as cnt
		   from (select DATE_FORMAT(FROM_UNIXTIME(brd.data_ts/1000),'%Y-%m-%d') as date_temp,
						brd.ota_version as ota_version,
						right(brd.uuid,12) as uuid_simple,
						brd.uuid,
						brd.trigger_id ,
						vim.vin,
						vim.vehicle_tested_by ,
						vim.int_vehicle_no
						from (select data_ts
									 ,ota_version
									 ,uuid
									 ,trigger_id
								from beta_raw_data dt
							   where EXISTS (select 1
											   from trigger_id_info_mngt tiim
											  where dt.trigger_id = tiim.trigger_id
												and tiim.is_ignore = 0  
											)
							{triggerIdStr} {otasStr}
							 ) brd 
						left join 
							 (select vin
									 ,vehicle_tested_by
									 ,int_vehicle_no
									 ,chery_no
								from vehicle_info_mngt 
							 ) vim 
						  on brd.uuid = vim.chery_no					
				) dt
          where dt.date_temp >= '{start_date}' 
		    and dt.date_temp <= '{end_date}' 
                {vehiclesStr}
	      group by trigger_id
          order by trigger_id;
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "tid": data[0],
                    "cnt": data[1],
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)




@dataServiceApp.route('/getBytesoupFilesQualityCount', method=['POST']) 
def getBytesoupFilesQualityCount():
    """
        [POST] /getBytesoupFilesQualityCount 数据质量分析-B/S文件质量分析 * Beta版  
 
        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
            ota版本号，逗号隔开
        trigger_id: str
            triggerId 多个用逗号隔开
        vehicles: str
            车辆uuid后12位，多个用逗号隔开
        Returns
        -------
        object
            JSON 数组
              [{
                    trigger_id:140003,
                    total_count:7,
                    get_info_count:0,
                    service_available_count:0,
                    all_count:0
                }]
    """
    json_params = request.json
    result = {
        "code": 200,
        "message": "success",
        "data": []
    }
    
    try:
        # 获取参数并提供默认值
        start_date = json_params.get("start_date")
        end_date = json_params.get("end_date")
        vehicles = json_params.get("vehicles", "")
        ota_versions = json_params.get("ota_versions", "")
        triggerId = json_params.get("triggerId", "")
        # 参数校验
        if not start_date or not end_date:
            result["code"] = 400
            result["message"] = "start_date and end_date are required"
            return json.dumps(result, ensure_ascii=False)
        start_date = date_str_to_timestamp(start_date)*1000
        end_date  = date_str_to_timestamp(end_date)*1000 + 86399000
        print('start_date',start_date)
        print('end_date',end_date)
        # 将vehicles和ota_versions转换为数组
        vehicle_list = [v.strip() for v in vehicles.split(',') if v.strip()] if vehicles else []
        ota_version_list = [v.strip() for v in ota_versions.split(',') if v.strip()] if ota_versions else []
        triggerId_list = [v.strip() for v in triggerId.split(',') if v.strip()] if triggerId else []
        sql = """
            SELECT 
                trigger_id,
                COUNT(*) AS total_count,
                SUM(CASE WHEN decompressed_failed = 1 THEN 1 ELSE 0 END) AS decompressed_count,
                SUM(CASE WHEN get_info_failed = 1 THEN 1 ELSE 0 END) AS get_info_count,
                SUM(CASE WHEN service_missing = 1 THEN 1 ELSE 0 END) AS service_available_count,
                SUM(CASE WHEN decompressed_failed = 1 OR get_info_failed = 1 OR service_missing = 1 THEN 1 ELSE 0 END) AS all_count
            FROM 
                bytesoup_files_quality
            WHERE 
                ts >= :start_date 
                AND ts <= :end_date
                {ota_versions_condition}
                {vehicles_condition}
                {triggerId_condition}
            GROUP BY 
                trigger_id
            ORDER BY 
                trigger_id;
        """
        
        # 动态构建SQL条件
        ota_conditions = ""
        if ota_version_list:
            ota_conditions = "AND ota_version IN :ota_version_list"
        
        vehicle_conditions = ""
        if vehicle_list:
            vehicle_conditions = "AND vid_short IN :vehicle_list"

        triggerId_conditions = ""
        if triggerId_list:
            triggerId_conditions = "AND trigger_id IN :triggerId_list"
        sql = sql.format(
            ota_versions_condition=ota_conditions,
            vehicles_condition=vehicle_conditions,
            triggerId_condition=triggerId_conditions
        )
        
        # 使用参数化查询
        params = {
            'start_date': start_date,
            'end_date': end_date,
        }
        
        # 只有列表不为空时才添加参数
        if vehicle_list:
            params['vehicle_list'] = vehicle_list
        if ota_version_list:
            params['ota_version_list'] = ota_version_list
        if triggerId_list:
            params['triggerId_list'] = triggerId_list
        result_data = session.execute(text(sql), params).fetchall()
        
        # 转换结果格式并处理 Decimal 类型
        def convert_decimal(obj):
            if isinstance(obj, Decimal):
                return float(obj)
            raise TypeError
        
        data_list = []
        for row in result_data:
            row_dict = {}
            for key, value in row._asdict().items():
                try:
                    row_dict[key] = json.loads(json.dumps(value, default=convert_decimal))
                except:
                    row_dict[key] = str(value)  # 如果仍然无法序列化，转为字符串
            data_list.append(row_dict)
            
        result["data"] = data_list
        
    except PendingRollbackError:
        session.rollback()
        result["code"] = 500
        result["message"] = "Database rollback occurred"
    except Exception as e:
        session.rollback()
        result["code"] = 500
        result["message"] = f"Server error: {str(e)}"
    finally:
        session.remove()
    
    return json.dumps(result, ensure_ascii=False)










